Hi, I am a developer. I recently created the Stored Procedure using high cpu memory consumption.
Can I reduce the cpu bottleneck or memory consumption used by Stored Procedure in creating clustered index or non clustered index?
when I altered column in non clustered index CPU consumption suddenly reduced but again few hours it again rising.....to high.
Is it the right way to alter the column of non clustered index to reduce memory consumption daily routine basis? IT Giants.........give me the suggestion...........
asked Sep 07, 2012 at 09:57 AM in Default
Based on what you've provided, it's hard to know what the answer is. To understand what to do to make a query run faster, first, you have to see the query. There are any number of things that could be wrong with it. Do you have functions on columns in the WHERE clause or in the JOIN criteria? Are you use appropriate data sets? Do you have nested views or multi-statement user-defined functions? These things, and oh so many more, can cause performance bottlenecks all by themselves.
If you think you've got a good handle on the T-SQL code itself, then you should examine the execution plan of the query to understand how the query optimizer within SQL SErver is interpreting what you wrote. You may find indications that an index is indeed needed.
In general, is an index the primary mechanism for reducing CPU usage? No. I wouldn't say it is. First is ensuring that the server is properly configured. Then you get into checking if the T-SQL is right, then that you have the right data structure.
For CPU to go down after applying and index and then go back up... I think I'd look to other causes. Also, how do you know for certain that your one query is the cause of the CPU spikes? There's not enough information here to tell you for certain what to do.
If you're really interested in learning how to tune queries, I'd suggest getting a copy of my book SQL Server 2012 Query Performance Tuning. There's also a 2008 version. For reading execution plans, I wrote another book.
answered Sep 07, 2012 at 10:35 AM
Grant Fritchey ♦♦