May 28, 2008 at 11:46 pm
Hello guys,
I'm new to this forum and I hope you can help with this serious question.
So, here's my problem:
I'm running a query (6 GB sql server 2005 db) on a table (about 40 columns), in which I select only a single column over a specified timespan.
The timestamp is specified as a datetime-datatype and there's an existing index for this column.
The machine is Core 2 Duo at 2 GHz with 2 GB RAM and a RAID-1 250 GB HDD.
The server consumes about 90 MB of RAM before the first query and is configured to consume 2 GB at a maximum.
Now I'm running this query for a 2-day timespan and I'm getting the result (43.000 rows) in only 3 seconds, while the CPU usage doesn't exceed 10 % and the SQL Server consumes 113 MB of RAM.
After that I'm running the same query for a 4-day timespan, but this time it takes about 4 MINUTES!! to
return 87.000 rows and the SQL Server consumes 1,5 GB!! of RAM after that query (CPU Usage < 10 % during the whole query).
Does anybody have an idea, why this query is that slow?! The result is only two times greater than the result before. Is it an I/O-problem? But I think that the server has enough memory, and the first query only consumes 113 MB of RAM, so there should be enough memory for the second query?!
Thanks in advance, Tobi
May 28, 2008 at 11:59 pm
Please post the Query plans (as xml-formatted attached files) for the 2-day and 4-day queries.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 29, 2008 at 12:13 am
Well, you're right! There's a difference - but why?! He should use the same index, shouldn't he?!
May 29, 2008 at 12:23 am
The reason you've got as difference is because of the number of rows.
The 2 day query is returning 42k rows and that number is (probably just) small enough that SQL is willing to do an index seeks and a key lookup (a very expensive operation.
The 4 day query is returning 87k rows. Too many for a key lookup to be effective, so you get a (parallel) clustered index scan. Essentially that's a read of the entire table.
Suggestion: Add the column Speed as an include column in your nonclustered index TimeStamp. That should eliminate the need for the lookup and get you just an index seek on both. Your query will very likely be faster for both 2 and 4 day.
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
May 29, 2008 at 12:28 am
Here are the defs for the table and for the indices. The problem is, that I'm using a SP with dynamic sql to dynamically change the sql statement to query a specified column. So it's not only the 'Speed'-column thats queried - all other columns could be queried, too!
May 29, 2008 at 12:38 am
Then I revise my suggestion - normalise your table.
You've got a very unnormalised design which will make it hard if not impossible to create covering indexes.
Alternative suggestion (that may be useless as I don't know what other queries read this table). Change your primary key to NONCLUSTERED and change the index on timestamp (which I assume is the insert time) to CLUSTERED.
NB. Test Carefully!!!!! This change may help some queries and hinder others. I'm making this suggestion without full knowledge of your system and the change may hinder other operations.
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
May 29, 2008 at 12:45 am
GilaMonster (5/29/2008)
Then I revise my suggestion - normalise your table.You've got a very unnormalised design which will make it hard if not impossible to create covering indexes.
Thanks, but what do you mean by unnormalized design? I know how to normalize a table, but I didn't find a way to better design that table. Splitting this table into 4 or 5 sub-tables will make it hard for me to query the data.
I'll adjust the index on the timestamp to clustered and the current clustered index to a non-clustered and try that again. Thanks again for your time, i appreciate this.
Edit: There're no other queries to that table than the SP that I mentioned. Maybe this helps...
May 29, 2008 at 12:55 am
It's the repeating groups I'm talking about
[LagSpeedGlueUnit1] [int] NULL,
[LagSpeedGlueUnit2] [int] NULL,
[LagSpeedGlueUnit3] [int] NULL,
...
[SteamTemperatureSection1] [int] NULL,
[SteamTemperatureSection2] [int] NULL,
[SteamTemperatureSection3] [int] NULL,
[SteamTemperatureSection4] [int] NULL,
Repeating groups should be split out into separate tables (1NF). It's a suggestion, so ignore if you want.
Since the query in question is the only one, then the cluster on TimeStamp should server you well.
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
May 29, 2008 at 1:04 am
You're right, I already considered splitting up this table, but I was afraid that I can't query the data for each separate column with a single SP -> would yield to one SP per group in my opinion and thats not how I wanted it to be. But I'll try to redesign that table anyway.
Is there a way to switch the Primary-Index to a non-clustered index?
May 29, 2008 at 1:08 am
tschoenig (5/29/2008)
Is there a way to switch the Primary-Index to a non-clustered index?
Not switch it. Drop the NC index, drop the primary key, create the clustered index, create the nonclustered primary key.
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
May 29, 2008 at 2:55 am
Hi again,
i've reconfigured the indices and it works great! Thanks for your suggestions...and yes I'll redesign the poorly normalized table 😉
Bye, Tobi
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply