Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
    
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. –  Aaron Bertrand Apr 1 at 16:35

1 Answer 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).

share|improve this answer
    
I don't know why this is running. No one is running it manually. –  k09 Apr 1 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? –  Thomas Stringer Apr 1 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). –  Aaron Bertrand Apr 1 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 at 8:04
    
Ok, that is the wait time. But what is/are the wait type(s)? –  Thomas Stringer Apr 2 at 11:54

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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