x

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...........

more ▼

asked Sep 07 '12 at 09:57 AM in Default

askmlx121 gravatar image

askmlx121
2.5k 59 72 77

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.

more ▼

answered Sep 07 '12 at 10:35 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
91k 19 21 74

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?

Sep 07 '12 at 11:17 AM askmlx121

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.
Sep 07 '12 at 11:23 AM Grant Fritchey ♦♦
ok thank you very much....grant fritchey....
Sep 07 '12 at 11:27 AM askmlx121
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x60
x34

asked: Sep 07 '12 at 09:57 AM

Seen: 576 times

Last Updated: Sep 07 '12 at 01:22 PM