SQL Profiler Query

Question

Post reply

SQL Profiler Query

Cossy

Old Hand

Points: 334
More actions
May 30, 2011 at 12:07 pm

#389620

Hi Guys,
First timer looking for some general advice...
I've been performing some analysis on an overnight activity that we carry out to identify opportunities to optimise/enhance it wherever possible.
I ran the Profiler and fed the '.TRC' file in to the DTA and bingo up pops a load of valuable suggestions for new clustered and non-clustered indexes. Once implemented, the new indexes represent significant savings. So far so good. 🙂
Now there's one part of the overnight process that runs for approximately 20 minutes with very low CPU on the system so I suspect that its spending a lot of the time in the database. I expected the DTA to provide some advice on this activity but it didin't. When I looked in t the table that is accessed in this section of the overnight run I discovered that it consists of approximately 500,000 rows.
The table already has an index for the predicate used in most of the query functions so there are no opportunities there.
I suspect that my only way forward with this huge table is to archive a load of the old data within it to reduce its size but I wanted to hear from the experts to see what other options may exist.
I've heard about 'Partitioning' but wasn't sure if that would require any application level changes to fully exploit.
To make matters worse we are still running SQL Server 2000.
Any ideas appreciated.
Cossy
(UK)

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Is this just a single query taking 20 minutes?

500k rows is not really that much...

If it's just a single query, can you post the actual execution plan?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Cossy Old Hand Points: 334 More actions · Answer 2

Hi Lutz,

Thanks for the reply.

You are right.

I dug a little deeper in to the stored procedure and there are five huge queries that relate to three tables which are all considerably larger than the table that I previously described. One of the larger tables has 4.5 million rows and is nearly 1.5GB in size.

Also, all five queries include where clauses that check where field 'A' in huge table '1' = field 'B' in huge table '2' if you know what I mean.

Given the size of the tables, reducing the processing time could be beyond the SQL Profiler. I think these tables could do with a bit of 'Archiving'!!

If you've got any more ideas I'd appreciate them.

Thanks again.

Cossy

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

Be very careful using DTA. It's prone to massively over-recommending new structures and often recommends far too wide ones too. Test each recommendation before implementing, consider the effects that the changes will have, implement just the ones that make sense.

Please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

LutzM SSC Guru Points: 107049 More actions · Answer 4

4.5mill rows is still not "large" for some of us.... 😉

If you could provide the actual execution plan for the two heaviest queries we migt be able to give you at least a hint.

Regarding DTA: don't take those recommendations too serious. More often than not the DTA advice doesn't help.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Cossy Old Hand Points: 334 More actions · Answer 5

Hi Gail,

The DTA recommendations have reduced the elapsed time of this overnight activity from 2 hours 30 minutes to 1 hour 15 minutes.

I will be checking the impact of the new indexes on the general overall performance of the 'Test' system and if there are any negative issues I'll certainly reconsider. If there aren't, I'll be hard pushed not to go with them.

I'm no SQL Server DBA although worked a while with Oracle(7.3-10.2).

I've noted the URL and will upload the info asap.

Thanks for your help.

Cossy

Cossy Old Hand Points: 334 More actions · Answer 6

Hi Gail,

I've just read the URL and its going to take a while to get the info.

Its a stored procedure that runs several large queries with variables etc. I'll have a chat with our developer tomorrow and see if he can help me run them in isolation outside of the stored procedure and to replace the vartiables with meaningful values.

We are also running SQL Server 2000 which isnt great. Can I run the SSMS from 2005 to get the execution plan for a query in SQL Server 2000?

Thanks again.

Cossy

Cossy Old Hand Points: 334 More actions · Answer 7

Hi Lutz,

Ditto my comments above.

As soon as I can work out a way of extracting those queries and obtaining the execution plans Ill upload the data.

Thanks again.

Cossy

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 8

cossy.cosmas (5/30/2011)
The DTA recommendations have reduced the elapsed time of this overnight activity from 2 hours 30 minutes to 1 hour 15 minutes.

Not all that impressive honestly.

I will be checking the impact of the new indexes on the general overall performance of the 'Test' system and if there are any negative issues I'll certainly reconsider. If there aren't, I'll be hard pushed not to go with them.

Test them one by one, implement ones that help, leave off ones that don't. You'll probably find that one or two (or a very small percentage) make most of the improvement.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Cossy Old Hand Points: 334 More actions · Answer 9

Will do Gail.

Thanks again.

I'll upload all of the information as soon as I can work out how to do it :O)

Cossy

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 10

cossy.cosmas (5/30/2011)
Can I run the SSMS from 2005 to get the execution plan for a query in SQL Server 2000?

There's a SQL 2000 section of that article.

Please in future post SQL 2000 questions in the SQL 2000 forums. If you hadn't told us, we'd have been recommending SQL 2005-specific features for you.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Cossy Old Hand Points: 334 More actions · Answer 11

Funny thing is that one part of the overnight activity actually increased from 10 minutes to 20 minutes despite the overall saving.

I'm starting to think that maybe one of the new indexes caused this. I'll apply them one by one and see which one introduces the delay.

Thanks again.

Cossy

Cossy Old Hand Points: 334 More actions · Answer 12

Hi Gail,

One portion of the overnight run actually doubled in length from 10 minutes to 20 minutes. Based upon what you have said I'm starting to think that maybe this was introduced by a new 'unneccesary' index.

I'll implement them one at a time and record the impact of each.

If I can save that 10 minutes Ill be very pleased.

Thanks again.

Cossy

Cossy Old Hand Points: 334 More actions · Answer 13

Hi Gail,

One portion of the overnight run actually doubled in length from 10 minutes to 20 minutes. Based upon what you have said I'm starting to think that maybe this was introduced by a new 'unneccesary' index.

I'll implement them one at a time and record the impact of each.

If I can save that 10 minutes Ill be very pleased.

Thanks again.

Cossy

Cossy Old Hand Points: 334 More actions · Answer 14

Hi Gail,

One portion of the overnight run actually doubled in length from 10 minutes to 20 minutes. Based upon what you have said I'm starting to think that maybe this was introduced by a new 'unneccesary' index.

I'll implement them one at a time and record the impact of each.

If I can save that 10 minutes Ill be very pleased.

Thanks again.

Cossy