for analyzing the execution plan, check out SQL Sentry Plan Explorer. They have a free version, and it's a much better tool for execution plan analysis than Management Studio
On Thu, Dec 5, 2013 at 10:03 AM, Mark A Kruger <mkru...@cfwebtools.com>wrote: > > Brooke, > > Couple of points of inquiry. > > 1) 50k records can be a little OR a lot. How much actual data is returned. > Is a lot of textual? Management studio might look really fast but the > problem could be a lot of character data buffering to the web server. > > 2) have you looked at the activity monitor? Filter by your connection and > watch for blocks or waits - taking note of the process blocking. > > 3) Indexing might be ok but maybe not. Take a look at the "execution plan" > in Management Studio - it can tell you what the most expensive operations > of > the query are. > > 4) Double check parallelism on the server. This can bite you under certain > conditions and will result in what look like "randomly slow" queries with > no > seeming blocks. See my blog post about it: > http://www.coldfusionmuse.com/index.cfm/2011/11/18/cf.mssql.parallelism > > Hope this helps a little. Good luck! > > -mark > > > > > -----Original Message----- > From: Brook Davies [mailto:cft...@logiforms.com] > Sent: Thursday, December 05, 2013 11:26 AM > To: cf-talk > Subject: Simple SQL Query sometimes really Slow? > > > 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:357293 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
