I am trying to tune a really busy SQL environment. I have been able to successfully implement new indexes and have been able to eliminate all of the long running and high read SQL from the environment. I no longer have bad query plans (for the most part). I still have very high cpu utilization.Some background: The box this is running on is an 8 core opteron HP DL585 w/32GB RAM. The instance this database is on has 20GB RAM allocated. The OS is x64, the SQL Server is x64 EE. the OS is current w/service packs and hotfixes. SQL Server is at post SP2 build 3186. The buffer cache hit ratio is 99.8%. The cpu utilization is 90-100% all of the time. Disk queuing is minimal (SAN connected to high-end Hitachi SAN).I can't really post any information about the tables or the queries here or any sample data. I know that makes it tough for anyone to give assistance, but I am really not in need of help optimizing queries or tuning indexes, etc.Instead, I am a little stumped on how to reduce the 'parse and compile time' for this query and that is what I really need help with. For example, here is STATISTICS IO and STATISTICS TIME output for one of 2 queries that is basically burying this box:SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 1 ms.SQL Server Execution Times: CPU time = 0 ms, elapsed time = 1 ms.SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 619 ms.Table 'OBFUSCATED1'. Scan count 0, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'OBFUSCATED2'. Scan count 4, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'OBFUSCATED3'. Scan count 0, logical reads 12, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'OBFUSCATED4'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.(1 row(s) affected)SQL Server Execution Times: CPU time = 0 ms, elapsed time = 6 ms.SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 1 ms.SQL Server Execution Times: CPU time = 0 ms, elapsed time = 1 ms.
The query plan looks as good as it can be. The duration I get on this query (~650ms, matches with what I see when I trace with profiler). The tables in play here are fairly large, 40+ million rows in one and 114 million in another. Query plan is all index seeks. The application uses ad-hoc queries for everything with no use of prepared statements and very little use of stored procedures. That is something that I won't be able to change. Aside from that, everything looks good except for the parse and compile time. btw, the procedure cache on this host looks like this - some info snipped out - sizes in MB:SQL Plans 3593.234375TokenAndPermUserStore 288.406250Default 205.132812SOS_Node 88.070312SchemaMgr Store 87.765625Object Plans 86.851562SNIPacket 26.015625Bound Trees 20.062500SystemRowsetStore 7.640625sxcCacheStore 3.609375
3.5GB for SQL Plans seems pretty large. My guess is that system is spending a long time scanning through the procedure cache looking for a plan for this query and that takes 619ms to do. My thinking is that this is as good as it gets until the code is changed to use stored procs and prepared statements for greater efficiency. Any ideas? Have you solved a problem like this? THanks. -ec
How can I reduce high SQL parse and compile times?
Posted - 2007-12-06 : 16:59:47
Another option - do you know about plan guides? I have not used them in anger but you can effectively force a statement to use the plan you specify instead of compiling a new plan.