2

We are seeing the below query as regularly having very high wait stats.

What does this query relate to? System tables? Is it possible to reduce it's wait time?

Thanks!

select @reservedpages = sum(a.total_pages),
@usedpages = sum(a.used_pages),
@pages = sum(
CASE
-- XML-Index and FT-Index-Docid is not considered "data", but is part of "index_size"

When it.internal_type IN (202,204) Then 0
When a.type <> 1 Then a.used_pages
When p.index_id < 2 Then a.data_pages
Else 0
END
)
from sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id
left join sys.internal_tables it on p.object_id = it.object_id
1
  • How long is "very high"? What are the actual wait types? How frequently is the query being run? Is the waiting actually causing a problem? Something will always be the highest wait, that doesn't mean you should always be "solving" that problem. Apr 1, 2014 at 16:35

1 Answer 1

1

That looks like a snippet from the definition of the sys.sp_spaceused system stored procedure.

regularly having very high wait stats

What are the waits pertaining to the execution of this stored procedure?

I'd be even more interested to find out why sys.sp_spaceused is running so frequently that it is negatively affecting performance. That stored procedure isn't typically going to be run regularly, or frequently if regularly.

Depending on what version of SQL Server you are using, there are a few ways to find out who (host name, application name, etc.) is kicking this stored procedure off. My favorite way to do this would be through the use of Extended Events (if you are only SQL Server 2008 or higher. You haven't specified version in your question as of now).

6
  • I don't know why this is running. No one is running it manually.
    – K09
    Apr 1, 2014 at 15:49
  • @K09 the next step would be to find out what/who is running it. What version of SQL Server are you using? Apr 1, 2014 at 15:50
  • 1
    ...and what monitoring tools are you using (they may be for SQL Server, or they may be more generic, e.g. SCOM). Apr 1, 2014 at 16:07
  • I am using SQL Server 2008R2. I am trialing the Idera Diagnostic Manager and this states Wait time of 50000m/s for this query!
    – K09
    Apr 2, 2014 at 8:04
  • Ok, that is the wait time. But what is/are the wait type(s)? Apr 2, 2014 at 11:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.