Just for clarification, do you have a unique index with the three non-primary columns, or are those indexed individually? If individually, I would suggest a combined index, at least of the usr_id and form_id columns, as those are numeric values and then a separate index of the date column. Ive found mixing datatypes within an index usually doesnt gain much in query performance.
Since 2008, SQL Server has the default Lock Escalation setting as Table, which means that the processing of large updates will lock to the table. You can see the specifics of whats happening under-the-hood with each of the escalation settings here: http://msdn.microsoft.com/en-us/library/ms190273.aspx You might try setting Lock Escallation to DISABLE and see if that resolves the issue. You can also turn on Snapshot Isolation to allow your reads to proceed, even when large updates are happening: http://msdn.microsoft.com/en-us/library/tcbchxcb%28VS.80%29.aspx HTH, Jon On Dec 5, 2013, at 12:26 PM, Brook Davies <[email protected]> wrote: > > This may not be the right place to post this (man, CF-TALK has changed a lot > in the last 5 or so years ;)). > > > > I have a simple SQL query that is showing up as running slow. When I run it > via the Management Studio it is sometimes fast 0.1 seconds and sometimes, > seemingly randomly slow 1.5 minutes!). Other queries on other tables are > executing normally. This table only has 50k records and even a simple query > is sometimes really slow. > > > > The query that runs slow is as simple as > > > > select commitDate,id from databaseChangeLog > > where usr_id = 62622 and form_id = 312468 > > and commitDate > '2013-12-04 11:00:05.0' > > > > But is just as slow without the date part. The table has a clustered index > on the primary key (id) and a non-clustered index on usr_id,form_id and > commitDate. The index doesn't seem to make any difference. > > > > My guess is the table is locked. My question is: > > > > How can I determine if it is locked? What would be locking it. I checked all > my code and there are no CFTRANSACTIONS or ISOLATED READS or anything like > that. There are some inserts and the table has 2 TEXT columns which are > being updated at times with fairly large values. But the only queries > reported as slow are these simple SELECTS. The query execution plan uses the > non-clustered index on (usr_id,form_id and commitDate). > > > > I'm just at a loss as to why this specific query is sometimes so slow.. > where to look? > > > > Brook > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:357291 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
