Re: Queries Occasionally Slowing Down

On Nov 20, 7:35 pm, bob laughland <peter.mcclym...@xxxxxxxxx> wrote:
On Nov 19, 10:01 am, Erland Sommarskog <esq...@xxxxxxxxxxxxx> wrote:



bob laughland (peter.mcclym...@xxxxxxxxx) writes:
select * from DataStore where dataStoreName = 'name' and rowid in
(select RowId from DataStore where dataStoreName ='name' and name =
'keyname' and value = 'keyvalue') order by rowid

Are those 'name' etc, literals expanded into the query, or are they
parameters?

I ran the profiler to see what I could see and I found something
interesting. The profiler has a number of columns per SQL statement
that is run, CPU, Reads, Writes, Duration.

For the queries that are running fine the stats are like this

CPU - between 0 and 16
Reads - between 20 and 70
Writes - 0
Duration - between 2000 and 60000

But for one of the queries I caught which is running very slow the
stats were this,

CPU - 2574
Reads - 8944
Writes - 24
Duration - 9243528

Are there updates to this table? To me it looks like there are autostats
kicking in, and that would explain increase in reads and duration.

When you ran profiler, did you include StmtCompleted in the events? If you
do, I would expect that you will see a number of "SELECT statman". Those
come from autostats. You can also include Recompile events in the trace..
(There is both SP:Recompile and SQL:StmtRecompile.)

--
Erland Sommarskog, SQL Server MVP, esq...@xxxxxxxxxxxxx

Links for SQL Server Books Online:
SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000:http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Thanks for those suggestions.

You mentioned sp_who. That is not really a viable option here because
I have no way to know when the slow query will kick in. Is there any
other way to check the locking?

Someone asked whether the strings were literals or parameters.
Initially they were literals, but I changed it to parameters because I
have a basic understanding as to what difference that can make. It
didn't help overall, but was a good thing to add in anyway.

When I ran profiler I just used the default settings. So I will look
into your suggestion of autostats (it will have to wait till
tomorrow). It does make some sense but perhaps does not suggest why
the same query is affected. As I say I will check this anyway.

Another point asked was whether there were inserts done on this table.
There are a lot of inserts done, using bulk insert. But the parts of
the software which are running the queries are in a single thread, so
the SQL will be sequential. Perhaps an insert like that locks longer
than the time period the software sees as the time to run the bulk
insert?

Thanks.

After giving it another read I think I misunderstood your suggestion
of whether there are lots of inserts. You are saying that lots of
inserts can affect the autostats rather then it affecting locking.

There are stacks of inserts into the table overall.
.

Relevant Pages

  • Re: ADO.NET query execution much slower than SQL Management Studio
    ... Stats being compiled on SQL Server ... If you are dynamically adding statements in a sproc, ... compare this to the profiler command (meaning what is actually run in the ... Duration: 440 ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Queries Occasionally Slowing Down
    ... (select RowId from DataStore where dataStoreName ='name' and name = ... that is run, CPU, Reads, Writes, Duration. ... Links for SQL Server Books Online: ... I have no way to know when the slow query will kick in. ...
    (comp.databases.ms-sqlserver)
  • Re: Sql 2005 Profiler
    ... There's a setting in 2005 Profiler to show durations in mictroseconds instead of milliseconds. ... Tibor Karaszi, SQL Server MVP ... duration of over 3 million. ...
    (microsoft.public.sqlserver.tools)
  • Re: Queries Occasionally Slowing Down
    ... (select RowId from DataStore where dataStoreName ='name' and name = ... 'keyname' and value = 'keyvalue') order by rowid ... The profiler has a number of columns per SQL statement ... that is run, CPU, Reads, Writes, Duration. ...
    (comp.databases.ms-sqlserver)
  • Re: Sql 2005 Profiler
    ... In SQL Server 2000, the server reported both duration and CPU time ... I'm using a trc file to do the load testing through profiler. ...
    (microsoft.public.sqlserver.tools)
Loading