SQL Server tweaking

[generelz]

I am working on a content management webapp. I have one instance of a folder with a large amount of content in it (2500 pages), and I am noticing that when I load that folder in SQL Server the CPU on my machine spikes for 15-20 seconds while rending the folder content.

All of the content is stored in a database, so when I say "folder" that folder is represented by a record in a table, as well as each page being a record in another table.

I suspect that the query generated to get these pages from the folder is something along the lines of SELECT (some fields here) FROM page_table WHERE parentFolderId = 'theParentFolderId', which in general should be a highly optimized query because I have the parentFolderId indexed.

I have captured a trace file from this use case and run it through the profiler for suggestions on index strategies and the suggestions it makes have yet to yield any significant performance increases, so I am starting to think it is more an issue with how the server/database is set up rather than my indexing schemes or table structures.

What I am seeing is that the memory usage is not going up, however the CPU usage is spiking.

Has anyone experienced this before? Does anyone know of good resources available for tweaking SQL Server? I am running the 2005 developer edition.

Thanks for any help you can provide!

 

[null]

can u post the qry?

 

[Cardboard Hammer]

I am working on a content management webapp. I have one instance of a folder with a large amount of content in it (2500 pages), and I am noticing that when I load that folder in SQL Server the CPU on my machine spikes for 15-20 seconds while rending the folder content. ...

Check the Processes CPU usage to determine which process specific process is eating the cycles. (Maybe you did, but your post suggests that you just looked at overall CPU usage.) Are you viewing the webapp on the same machine? (The comment about rend[er]ing the folder content strongly suggests that you are.) If so, it's probably your browser that's causing the CPU spike (and you should consider writing the webapp to not throw such a monstrous amount of work at the browser all at once.) If not, is the web server on the same machine? If so, that could be what's eating the cycles.

...

I suspect that the query generated to get these pages from the folder is something along the lines of SELECT (some fields here) FROM page_table WHERE parentFolderId = 'theParentFolderId', which in general should be a highly optimized query because I have the parentFolderId indexed.

...

Unless the parentFolderId index is a clustered index, or includes all the fields used in the query (a.k.a. covering index,) the query isn't "highly optimized" as it will have to jump all over to collect all the data needed for the response. (However, I'd suggest you look to ensure that it is indeed SQL Server eating the cycles before you investigate this as the possible cause.)

...

Another possibility is inefficient querying that fails to scale well. A single, simple SELECT query that grabs all the records in a single batch shouldn't be a problem. However, if the application issues queries inside a loop that processes the results of another query, or if the query includes a correlated subquery (especially a poorly written one), that would be a big problem. (I've actually seen examples of both in systems I was asked to examine for performance issues. Neither hurts much if the record count of the outer query is quite small, but the amount of work increases linearly with the number of records in the outer query. (In the case of a correlated subquery, this number can be substantially larger than the number of rows returned by the query.))

 

[generelz]

Check the Processes CPU usage to determine which process specific process is eating the cycles. (Maybe you did, but your post suggests that you just looked at overall CPU usage.) Are you viewing the webapp on the same machine? (The comment about rend[er]ing the folder content strongly suggests that you are.) If so, it's probably your browser that's causing the CPU spike (and you should consider writing the webapp to not throw such a monstrous amount of work at the browser all at once.) If not, is the web server on the same machine? If so, that could be what's eating the cycles.

You're completely correct here, I'm sorry I did not specify. This is a development machine so everything is running together locally (appserver, sql server, and browser). I have investigated in the process explorer and it is indeed the SQL Server process that has both CPUs pegged while rendering the folder content.

Unless the parentFolderId index is a clustered index, or includes all the fields used in the query (a.k.a. covering index,) the query isn't "highly optimized" as it will have to jump all over to collect all the data needed for the response. (However, I'd suggest you look to ensure that it is indeed SQL Server eating the cycles before you investigate this as the possible cause.)

Another possibility is inefficient querying that fails to scale well. A single, simple SELECT query that grabs all the records in a single batch shouldn't be a problem. However, if the application issues queries inside a loop that processes the results of another query, or if the query includes a correlated subquery (especially a poorly written one), that would be a big problem. (I've actually seen examples of both in systems I was asked to examine for performance issues. Neither hurts much if the record count of the outer query is quite small, but the amount of work increases linearly with the number of records in the outer query. (In the case of a correlated subquery, this number can be substantially larger than the number of rows returned by the query.))

I see what you are saying about the indexing not being as efficient as it could be; I must admit I am fairly inexperienced when it comes to working with SQL Server.

We are using a J2EE appserver so the queries are generated automatically by the container - hence I don't really have much control over the exact query syntax generated, though I would like to think that it is relatively efficient. I have not looked at the exact queries generated yet, though this is possible using a JDBC logging driver. It may be something I will have to persue.

Really, this seems to be to be a relative simply query - no joins or anything...just a SELECT by a non-primary key field which is indexed. It is baffling me why 1.) it takes so long and 2.) uses so much CPU.

Again thanks for any insight anyone can give.

 

[mikeblas]

As far as I can tell, you're just guessing. You odn't know what the query is; you're just saying it has to be a certain way--with no joins--but you say there are two tables invovled. A join seems pretty likely to me. You want to think that it is a good, efficient query, but you've never even seen it.

So then, on all those guesses, you still think that the query is taking too much CPU. Since you don't even know what the query is, why do you think it's using too much CPU?

Have you used SQL Server Profiler? That will let you capture the query (or queries!) that are running.

Given a query, you'll then want to see its execution plan, review the schema, and then think about what could be better about that plan.

If the query is dynamically generated, it may be compiling every time. Perhaps the jump is due to the compiler and optimizer running. What do the SQL Server performance counters tell you?

If you don't know how to do that, let us know and we'll try to walk you through it.

 

[Cardboard Hammer]

Not by any means discounting what mikeblas posted, but as a quick and dirty test, you could run something like SELECT * FROM page_table WHERE parentFolderId = 'theParentFolderId' in Query Analyzer. If that DOESN'T cause SQL Server to peg out CPU utilization similarly to what you've been seeing, that would strongly point to inefficient querying being the problem. (However, even if that DOES cause the same kind of heavy CPU utilization, you should still check out the actual query (or queries) being issued.)

 

[mikeblas]

If you try to guess at the query like CH suggests, please use SET STATISTICS ON before you execute the query. That will report the time spent compiling, executing, and so on; both wall time and CPU time. Plus, it'll show how much I/O is happening.

You can get similar information from the sys.dm_exec_query_stats DMV. The DMV is more accurate.

 

[generelz]

As far as I can tell, you're just guessing. You odn't know what the query is; you're just saying it has to be a certain way--with no joins--but you say there are two tables invovled. A join seems pretty likely to me. You want to think that it is a good, efficient query, but you've never even seen it.

So then, on all those guesses, you still think that the query is taking too much CPU. Since you don't even know what the query is, why do you think it's using too much CPU?

You're right. I'm guessing for the most part. It will definitely help to see the actual query/queries.

Have you used SQL Server Profiler? That will let you capture the query (or queries!) that are running.

Yes I have done some very basic stuff with it - loading the trace file and then profiling against the database with that. As I said the profiler suggested some improvement strategies that did not seem to have much of an effect (0-5% improvement).

Given a query, you'll then want to see its execution plan, review the schema, and then think about what could be better about that plan.

If the query is dynamically generated, it may be compiling every time. Perhaps the jump is due to the compiler and optimizer running. What do the SQL Server performance counters tell you?

If you don't know how to do that, let us know and we'll try to walk you through it.

I would love to take a look at the performance counters. How would I go about specifically seeing index usage, as well as any other things that might shed some light on the high cpu usage (i.e. the compiler/optimizer running on non-cached queries, etc.)? I know how to access the performance monitor but I am not sure on exactly which counters would be the most salient. I see some under "SQLServer: SQL Statistics" such as "SQL (Re-)Compilations/sec", etc. Would this be one to watch?

 

[mikeblas]

Yes I have done some very basic stuff with it - loading the trace file and then profiling against the database with that. As I said the profiler suggested some improvement strategies that did not seem to have much of an effect (0-5% improvement).

It sounds like you're thinking of the Index Tuning Wizard. I'm talking about the profiler, which will let you monitor the activity on the server. You can see the queries and commands that are being submitted to the server as they happen, and this will let you find the text for the query you're interested in investigating.

I would love to take a look at the performance counters. How would I go about specifically seeing index usage, as well as any other things that might shed some light on the high cpu usage (i.e. the compiler/optimizer running on non-cached queries, etc.)? I know how to access the performance monitor but I am not sure on exactly which counters would be the most salient. I see some under "SQLServer: SQL Statistics" such as "SQL (Re-)Compilations/sec", etc. Would this be one to watch?

Index usage isn't a performance counter. To see which index is being used, you'd get the query (as I described, using Profiler) then run it in SQL Server Mangement Studio. When you execute the query in SSMS, ask for the "actual execution plan". That will show you a graphical representation of the plan. You can save that out as an XML file, or you can use the SET SHOWPLAN_TEXT ON command before running your query and that will give you an textual version, which you can paste here and we can help you interpret.

Compilations per second would be interesting to monitor while the server is running. If the value is high (like, more than zero!), it suggests that your intermediate layer is being stupid about asking the server to recompile the SQL it is dynamically generating. Maybe you cant do anything about this except complain to the middle tier vendor; maybe they have some tweaks. Or, maybe we can come up with a clever hack to help out. We need to do lots more investigation, and get away from your guesses.