Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ksr39
Posting Yak Master
193 Posts |
 Posted - 2012-10-24 : 00:11:03
|
| Hi Experts,I need a small help in creating non cluster index on a table as we were facing a performance issue. The issue was in one of our production server I found that the CPU utilization is going high in peak till 100% and its fluctuating, so I found some queries which are utilizing the CPU resource more, So I run these queries in tuning advisor and got the recommendations to create a non-cluster index on a table but here the index need to be create on a single table but on 18 columns out of 21 in which 1 is a cluster index and 4 are non-cluster index already created.Please suggest me what I need to do and. Thank You All In AdvanceKRS39 |
|
|
lionofdezert
Aged Yak Warrior
885 Posts |
Posted - 2012-10-24 : 03:56:24
|
| Tuning Advisor,should only be used with complete load AND NOT for a single query.On which columns you need to create indexes, try to get this information by executing your query with execution plan. Moving your mouse pointer to costly nodes will give you predicate and seek columns infomation, these are the columns which should be part of a an index and then press F4 and in properties list down Output Columns, these are the columns which should be part of INCLUDE columns. (sometime Key columns are also part of output columns, so these should be excluded from INCLUDE list as they are already part of KEY columns)--------------------------http://connectsql.blogspot.com/ |
 |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-10-24 : 04:14:19
|
| Look at the system as a whole.Pobably the queries need to be rewritten - just adding indexes whenever you have an issue is going to lead to problems in the future as the server has to maintain them. You need to look at why these queries are being run and it is usually easy to rewrite them to use existing indexes or to add something small. The tuning advisor will just give a hint on how to optimse for what is being run so isn't a lot of use other than in the simplest cases.In this instance it is probably suggesting a covering index - maybe what you should do is change the query so that it gets the PKs of the rows needed (using small indexes) then uses that to get the data.Are these ad hoc queries - if so talk to the users and show them ho to write queries that will work.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
ksr39
Posting Yak Master
193 Posts |
Posted - 2012-10-24 : 05:46:01
|
| Hi Experts,Thank you for your suggestions, I will look in to this and get back to you mean while I need small information is there any way to find what all queries running and how many times the queries are running in the server for each day?Thank you in AdvanceThank You All In AdvanceKRS39 |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-10-24 : 05:57:35
|
| This is a lot easier if you have all dtabase access via stored procedures.What type of system is it - do you have an application, reports, ssis, bacth processing, oltp?What are the issues you are finding. Is it having issues at certain times (usually due to a few long running rogue queries) or is it short running queries that are being run too many times for the system to cope. The former is easy to diagnose and fix - the later might be a bit more tricky.If it is running slowly at a time just get a snapshot of everything that is running then - also look at the disk io and find the query that is using the most and look how long it has been running. That might show you what the issue is. Usually a couple of things cause the issue but have a knock on effect - it can look like other things are an issue but in fact they are being stopped by the bad queries - don't just look at how long things run look at the resources they are using.First thing to do is to get an idea of what is running - look at sysprocesses - I use http://www.nigelrivett.net/SQLAdmin/sp_nrInfo.html to show what is running and the query (might need one of the columns in a temp table increasing in size depending on your system).==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
|
|
Creating Non-Cluster Index