question

askmlx121 avatar image
askmlx121 asked

SP's optimization by index

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...........
indexoptimization
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Grant Fritchey avatar image
Grant Fritchey answered
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 S[QL Server 2012 Query Performance Tuning][1]. There's also a 2008 version. For reading execution plans, [I wrote another book][2]. [1]: http://www.amazon.com/Server-2012-Query-Performance-Tuning/dp/1430242035/ref=sr_1_1?ie=UTF8&qid=1347014063&sr=8-1&keywords=fritchey [2]: http://www.amazon.com/Server-Execution-Plans-Grant-Fritchey/dp/1906434026/ref=sr_1_1?ie=UTF8&qid=1347014105&sr=8-1&keywords=fritchey+execution
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
Not seeing the query, I'm just guessing here. But, if you're modifying the order of the key columns of an index and you're getting a performance improvement, it's likely that the first order wasn't as selective as the new order so the optimizer either didn't use the index at all, or, it used it but scanned it instead of running a seek on it. Again, I'm just guessing because I can't see the query or the execution plan, your structure or your data. As to tuning for CPU, no, I don't generally do that. I try to look at everything about the behavior of the query, cpu, io and execution time. The two biggest measures for me are io and execution time, especially because, even with SSDs, the slowest part of the process is reading and writing to disk.
1 Like 1 ·
askmlx121 avatar image askmlx121 commented ·
hi grant fritchey, thank you for your answer... My question is if I altered the column of non clustered index like create non clustered index idx_sid on employees(empid,empname,activeflag)--before CPU consumption of my_sp is total CPU time: 108162120.65,avg cpu time:893.65 alter non clustered index idx_sid on employees(empid,activeflag,empname)--after alter CPU consumption of my_sp is total CPU time: 162120.23 avg cpu time:65.13 After alteration in non clustered index column, CPU consumption of my_sp reduced in every time am doing..... my question is why was it reduced the CPU time 108162120.65 to 162120.23? Is it also one of the method of reduce the CPU bottle neck? is it the right way to do that?
0 Likes 0 ·
askmlx121 avatar image askmlx121 commented ·
ok thank you very much....grant fritchey....
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.