SQL Server 100 cpu diagnose

Question: Users are reporting long delays in query responses. I checked on the server and noticed CPU counter at 100 % on Performance Monitor. There are 4 cores associated to this server and all the cores were hovering around 100%. How can I diagnose the underlying cause ? Answer: It’s a common problem faced by DBAs , where the server is sitting at 100% for prolonged periods. Check there are no other processes outside of SQL Server which are causing the server to be at 100%. Typically security scans, virus scans and monitoring can be root causes. I have also... Read more →

Guiding principles of wait time analysis

Question: Could you send me some information about the guiding principles of wait time analysis? Answer: Wait time analysis for SQL Server performance tuning is a methodology covering a wide range of problem sets. But at its core are some guiding principles. 1) Identify what the impact is on the end user 2) The main focus is on aiming to to make a positive impact on the application end user. This is in contrast to aiming to deliver a better cache hit ratio or cpu usage etc. This doesn’t mean that cache hit ratio and cpu usage are not important... Read more →

Will placing a large data column as an index INCLUDE impact sql query performance

Question: I’m thinking of placing a large data column as an INCLUDE into an index , a nvarchar(4000). Will it have a negative impact on SQL Server performance? Basically, is it a good idea? I’ve read it’s not a good idea. Answer: I can’t advise you to never implement such an action, but in general I’d avoid placing such a large data column as an INCLUDE of an index. Some of the well documented benefits of including non key columns in the INCLUDE statement is data types not allowed as key columns are allowed and the INCLUCDE columns are disregarded... Read more →

ERROR 8623 The query processor ran out of internal resources and could not produce a query plan

Question: In the SQL Server Error Logs I saw the following message: The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query Is there a way to find the query ? I know I can use something like Extended Events – which would allow me to trap the sql text , but what about finding a query that has executed and triggered this error Answer: If you... Read more →

Show me the wait stats

Using wait stats to identify pressure points on your SQL Server is a common method for troubleshooting. Unfortunately , it is very easy to jump to conclusions about problems on the basis of the highest wait stats. My general approach is to use wait stats , but letting them guide me to extra analysis. There could be all sorts of reasons why a wait stat or a combination of wait stats appear . The more you use wait stats , you develop methods for troubleshooting. Interpreting the initial findings will help you start to ask the right question. These are... Read more →