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-talk@houseoffusion.com>
> 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:357292
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
