January 30, 2009 at 8:40 am
I have a table that is 16 columns wide, and has 1000 (random, unique) inserts per second.
They run ad-hoc queries, that are taking too long to return. Here's some examples of the types ad-hoc queries they run:
select * from logger2 where KeyId = '084e0348-bf56-460a-8067-e243689b6bc7'
select * from logger2 where (severity=2 or severity=1)
select * from logger2 where username = ‘joe’
Should I look into indexing this table for 'ad-hocs'?
Could the new 'Forced Parameterization' or sp_executesql be of use to me on this? (it's a SQL 2005 DB) I'm guessing that making the user use parameterized queries instead of the above queries would be much faster?
Any help or ideas greatly appreciated. I'm not used to having to deal with ad-hocs.
January 30, 2009 at 12:59 pm
The adhoc queries will cost more in terms of compile time but that is probably small compared to the read time. They may be getting autoparameterized anyway if they are very simple (and will generate consistent plans regardless of the parameter value). It's more likely that they are experiencing blocking by the inserts. Indexing may help (although obviously it will hurt insert performance) if it changes the plans to use seeks instead of scans.
Why is there a need for adhoc queries against this table? Is there a requirement for those queries to be against live data (as opposed to say hour-old or day-old data)?
February 2, 2009 at 6:28 am
krypto69 (1/30/2009)
I have a table that is 16 columns wide, and has 1000 (random, unique) inserts per second.They run ad-hoc queries, that are taking too long to return. Here's some examples of the types ad-hoc queries they run:
select * from logger2 where KeyId = '084e0348-bf56-460a-8067-e243689b6bc7'
select * from logger2 where (severity=2 or severity=1)
select * from logger2 where username = ‘joe’
Should I look into indexing this table for 'ad-hocs'?
Could the new 'Forced Parameterization' or sp_executesql be of use to me on this? (it's a SQL 2005 DB) I'm guessing that making the user use parameterized queries instead of the above queries would be much faster?
Any help or ideas greatly appreciated. I'm not used to having to deal with ad-hocs.
If your table has no indexes at all, yes, you should at least get a clustered index on it. Use the most frequently accessed data path (not necessarily the primary key) as the clustered index (assuming it's a somewhat narrow set of data).
As to the rest... you'll have to test it. I would absolutely suggest using sp_executesql. You're more likely to get plan reuse. Forced Parameterization might, or might not, help you. You'll need to test it.
You could also consider taking a look at SQL Server 2008. It has the capacity to create plan stubs for ad hoc queries that reduces the memory load in the plan cache.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 2, 2009 at 9:04 am
Thanks for answering Matt,
Yes this table is kind of a troubleshooting table. So, there are techs that need to see real time info.
I was trying to think of a way I could use a force them to use a parameterized query instead of select *...
February 2, 2009 at 9:05 am
Thanks Grant,
I'll experiment with both those options and look into using 08.
February 2, 2009 at 9:18 am
If the table has a timestamp you could index it and have them include it in all queries - presumably they only want to retrieve recent information. If they need older information, can they query a copy of the table?
February 2, 2009 at 9:25 am
You'd have to know before-hand what kind of stuff they query.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 2, 2009 at 9:30 am
It does have a timestamp, I will try that.
THANKS MATT
Is there some way I can force/require them to use a date (or date range)?
February 2, 2009 at 9:32 am
You'd have to know before-hand what kind of stuff they query.
- GSquared
I think I can get a pretty good list of the kinda stuff they query the most, but I'm kinda lost on what you mean..can you elaborate?
February 2, 2009 at 9:54 am
krypto69 (2/2/2009)
You'd have to know before-hand what kind of stuff they query.
- GSquared
I think I can get a pretty good list of the kinda stuff they query the most, but I'm kinda lost on what you mean..can you elaborate?
To put together indexed, parameterized queries, you'd need to know before-hand what kind of queries they are going to write.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 2, 2009 at 9:58 am
The only way you can force them to use the correct query is to create procs and deny them read access to that table - it probably won't be as easy as that 🙂 In the end it's in their own interests to shorten the query times - they might just need a push in the right direction.
February 2, 2009 at 10:02 am
Oh I see what you are saying..thanks Gsquared
February 2, 2009 at 12:32 pm
Thanks Matt,
I was kinda thinkin I may create a simple VB front end, and write a proc or two.
February 3, 2009 at 9:31 am
1) nonclustered indexes are NOT helpful if the value you are searching for is not VERY specific (like <1% of the total rows in the table)
2) at 1K/sec inserts, index maint will be a factor in performance of inserts.
3) fragmentation will also be a significant issue
4) do you ever prune the data? Adding 86.4M rows per day to the table will get it bloated in a hurry
5) what about using (NOLOCK) or READ UNCOMMITTED on the SELECTs? They could be taking a long time to run simply because they are being blocked by the continuous stream of inserts. Did you check for blocking?
6) do you need ALL data from the table (SELECT * . ..)
7) be careful with clustered index choice. recall that a) if it isn't unique you get an additional 4-byte uniquefier for every row and b) the clustering key is carried on every non-clustered index. that first field you have looks to be a huge char type and would lead to a bloated index structure
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 3, 2009 at 11:19 am
While doing my testing (inserts are active) i was getting deadlocks and kicked out.
So I added the NOLOCK, that stopped the deadlock errors..so then..
I removed all indexes, and created a clustered on the datetime column. I then queried with and without the date. To my surprise there was no speed benefit to using the datetime clustered. I am getting a clustered index seek, I get a clustered index scan if I don't use the date. But no real increase in return times.
Any thoughts?
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply