February 3, 2009 at 11:47 am
How many rows in the table?
February 3, 2009 at 12:10 pm
krypto69 (2/3/2009)
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?
Please provide the script for the clustered index as well as the query you are using (with and without date filter). Also how many rows are returned for each query and what percentage of the table is that rowcount?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 3, 2009 at 1:23 pm
Will do ...thanks..
While I'm here would there be any benefit to me using
snapshot isolation?
February 4, 2009 at 8:18 am
krypto69 (2/3/2009)
Will do ...thanks..While I'm here would there be any benefit to me using
snapshot isolation?
Can you tell me how much investigation/study did you do about snapshot isolation prior to asking that question? 😎 At 1k/sec DML activity it is my guess that if you don't have plenty of CPU juice and MASSIVE tempdb capabilities you will be dead meat if you try snapshot isolation. All those modifications require lifting original data and dropping it down to tempdb, then your reader processes have to split their IO gathering between original unmodified data in the base table and pre-modified-copies of data from tempdb. You will be sooooo much better with NOLOCK or READ UNCOMMITTED isolation level - caveated that if you have a clustered index and you do UPDATES you can read data multiple times or miss reading data inflight. IIRC however you stated that it was purely insert activities on this table.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 4, 2009 at 8:42 am
Can you tell me how much investigation/study did you do about snapshot isolation prior to asking that question? At 1k/sec DML activity it is my guess that if you don't have plenty of CPU juice and MASSIVE tempdb capabilities you will be dead meat if you try snapshot isolation. All those modifications require lifting original data and dropping it down to tempdb, then your reader processes have to split their IO gathering between original unmodified data in the base table and pre-modified-copies of data from tempdb. You will be sooooo much better with NOLOCK or READ UNCOMMITTED isolation level - caveated that if you have a clustered index and you do UPDATES you can read data multiple times or miss reading data inflight. IIRC however you stated that it was purely insert activities on this table.
Very little research. The server does have allot of temp DB sapce, and this table is truncated every week. So, I don't think space would be an issue. However, your comments on I/O are duley noted.
I'll check into READ UNCOMMITTED. Is it possible to permenatly set that at a DB or table level? I don't have control over the Ad-hocs queries that the devs are using, so I can't force it at run time.
February 4, 2009 at 10:02 am
My comments on tempdb have almost NOTHING to do with SPACE. It is IO PERFORMANCE that matters. I can create a 1TB tempdb on a SINGLE DRIVE these days - and the IO performance will be a total DOG. 🙂 How many spindles back up that tempdb space you have- how many paths to the IO, etc, etc.
As for transaction isolation level, it is a connection setting that the ad hoc query users can set within SSMS/QA or whatever other tool they are using (usually). NOCOUNT is something they simply put in the SELECT statements they type in for each table/view referenced.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 4, 2009 at 12:21 pm
Thanks SQLGuru
So there's no way to enforce the READ UNCOMMITED at a system level?
I don't have any control over what the devs write in their ad-hoc queries. So, it would be tough for me to use the READ_UNCOMMITED as a solution.
February 4, 2009 at 12:49 pm
Even if you could set at a system level, isolation level is a CONNECTION level setting and is almost universally set by the subsystem doing the connecting as one of it's first actions and that would override any system setting. Each connection mechanism has it's own 'default' for this: ADOclassic, ADO.NET, SSMS, SQLCLIENT, ODBC, JDBC, QA, etc.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply