Simple SQL Query sometimes really Slow?

[hofar...@houseoffusion.com]

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:357289

[hofar...@houseoffusion.com]

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

Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:357290

[hofar...@houseoffusion.com]

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. I�ve found mixing datatypes within an index usually doesn�t 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 what�s 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

Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:357291

[hofar...@houseoffusion.com]

There are a couple of queries that you can run while your query is
executing to see if there is anything else running that is blocking the
execution. The first will show all running activities and identify if any
are blocking one another. The second is just a handy script to see what's
running currently.
/******************************************* Find blocking SPIDS
*******************************************/ SELECT s.spid, BlockingSPID =
s.blocked, DatabaseName = DB_NAME(s.dbid), s.program_name,
s.loginame, ObjectName = OBJECT_NAME(objectid, s.dbid), Definition =
CAST(text AS VARCHAR(MAX)) FROM sys.sysprocesses sCROSS APPLY
sys.dm_exec_sql_text (sql_handle) WHERE s.spid > 50
/******************************************* Find all running queries
*******************************************/ SELECT sqltext.TEXT,
req.session_id, req.status, req.command, req.cpu_time,
req.total_elapsed_time FROM sys.dm_exec_requests req CROSS APPLY
sys.dm_exec_sql_text(sql_handle) AS sqltext
If these don't show anything blocking your query, you may want to look at
using query hints to force the use of a particular index. SQLServer will
somtimes choose a poor execution plan. You can give it hints on which
index to use at the table level using something like the following: SELECT
* FROM tablename WITH (INDEX({indexname})) WHERE xxxx. A good primer on
using index hints can be found here:
http://blog.sqlauthority.com/2009/02/08/sql-server-introduction-to-force-ind
ex-query-hints-index-hint-part2/
Hope this helps,
-- Jeff

-------- Original Message --------
> From: "Brook Davies" <cft...@logiforms.com>
> Sent: Thursday, December 05, 2013 10:27 AM
> To: "cf-talk" <cf-...@houseoffusion.com>
> Subject: Simple SQL Query sometimes really Slow?
>

Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:357292

[hofar...@houseoffusion.com]

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

Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:357293

[hofar...@houseoffusion.com]

Could never figure this out, but we had a similar issue on 2005 with a date
time column.

I remember we changed from a cfquery to a stored procedure and it was
resolved.

Byron Mann
Lead Engineer & Architect
HostMySite.com

Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:357294

[hofar...@houseoffusion.com]

Thank you everyone for all your suggestions. Gonna start testing them out.

Byron: I have this issue when running the query via management studio and
via cfquery so not sure if that is relevant..

Jon: I'm still running 2005 (if it ain't broke...), but was also thinking
about trying READ UNCOMMITTED (maybe that would have the same effect as your
suggestion?)

Mark: The select is only returning a couple of columns (date/int) and no
text.. I'll check the activity monitor.. the execution plan shows the index
usage and doesn't appear to account for the delays...

Jeff: Thanks for the Queries, I'll try them!

Whohoo! Cftalk is alive!!

Brook

Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:357295