SQL Server Performance

Too many reads for simple querys

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by zimzum, Feb 6, 2009.

  1. zimzum New Member


    Dear Friends, I 've having some troubles tuning some querys that, at simple sight, are really easy, but even the SQL Profiler and the SQL Tuning advisor, they make too many reads. It`s a query that works on a 36000 rows table having some joins with really simple and small tables. The result is never more than 10 or 15 rows, but every sessions makes one of these queries every 5 seconds. I settled all the indexes that were needed but it is still making 77000 reads, and taking about 250 milliseconds to run. I think it is still TOO MUCH.



    What is the next thing I can do to make it work better? Have I got to check out the execution plan?





    By the way, How can I know how many transactions per second is the highest amount of transaction my SQL Server can run given the hardware we have??



    Ok, thanks in advance, you all seem to know so much about these stuffs...

    Zim

  2. rohit2900 Member

    [quote user="zimzum"]

    What is the next thing I can do to make it work better? Have I got to check out the execution plan?

    [/quote]
    Check whether you've proper indexes in place to cover your query.
    [quote user="zimzum"]

    By the way, How can I know how many transactions per second is the highest amount of transaction my SQL Server can run given the hardware we have??

    [/quote]
    Are your getting any performance issues as their is not bench mark set for this.
    Apart from this if possible can you post you query.
  3. zimzum New Member

    Yeap, I have already settled the indexes as SQL Profiler told me.


    This is the query:

    SELECT U.Name ,UT.
    Code:
     as UserState, ST.[Code] as SessionState,S.[StateDescription] as SessionStateDescription
    FROM Users U
    INNER JOIN UserStates UT ON UT.[ID]=U.[StateID]
    INNER JOIN Sessions S ON S.[UserID]=U.[ID]
    INNER JOIN SessionStates ST ON ST.[ID]=S.[StateID] 
    WHERE ST.[Code] NOT IN ('End') 
    AND S.[UserID] IN ( SELECT ID FROM Users WHERE CompanyID=@CompanyID ) 
    AND U.[ID]!=@UserID
    
    Where UserStates and SessionsStates are really small tables (4 or 5 rows)
    Users doesn't have more than 100
    BUT Sessions has about 36000 rows. 99% of them ended sessions.
    
    So I am asking the active sessions for all the users of a given company. The result should be ONLY ONE single record.
    
    I could rewrite the query (may be writing "IN ('ACTIVE','SLEEPING') " instead of "NOT IN('END')"), but shouldn't applying good indexes be enough?
    
    If not, which are the "not so obvious" rules or tips that I should consider when analyzing some query? This query can successfully run with no more than 50 reads , not 77000.
    
    Ok, You have been so kind, really.
    
    
    Looking forward for your response.
    
    Zim
    
     
  4. Adriaan New Member

    You haven't told us the details of the indexes ... anything could be frightfully wrong there, even if suggested by SQL Profiler.
    The last criteria with the != operator might also be a performance wrecker ...
  5. rohit2900 Member

    [quote user="zimzum"]Yeap, I have already settled the indexes as SQL Profiler told me.


    SELECT U.Name ,UT.
    Code:
     as UserState, ST.[Code] as SessionState,S.[StateDescription] as SessionStateDescription
    FROM Users U
    INNER JOIN UserStates UT ON UT.[ID]=U.[StateID]
    INNER JOIN Sessions S ON S.[UserID]=U.[ID]
    INNER JOIN SessionStates ST ON ST.[ID]=S.[StateID] 
    WHERE ST.[Code] NOT IN ('End') 
    AND S.[UserID] IN ( SELECT ID FROM Users WHERE CompanyID=@CompanyID ) 
    AND U.[ID]!=@UserID
    
    [/quote]
    What type of index is on session.userID,
    [quote user="zimzum"]
    
     
    SELECT ID FROM Users WHERE CompanyID=@CompanyID
    
    [/quote]
     This query will give distinct records or duplicate?
    Please post the index details for all the tables as suggested by Adriaan
    
  6. ScottPletcher New Member

    Please try this:
    SELECT U.Name ,UT.
    Code:
     as UserState, ST.[Code] as SessionState,S.[StateDescription] as SessionStateDescription
    FROM Users U
    INNER JOIN UserStates UT ON U.[ID]!=@UserID AND UT.[ID]=U.[StateID]
    INNER JOIN Sessions S ON S.[UserID]=U.[ID]
    INNER JOIN SessionStates ST ON ST.[Code] NOT IN ('End') AND ST.[ID]=S.[StateID] 
    WHERE [EMAIL]U.CompanyID=@CompanyID[/EMAIL]
    
  7. zimzum New Member

    Fine... I can see then that even after doing everything as the profiler says, you might be doing things terribly wrong and don't even know it.
    The query I posted could be solved perfectly just reading a few rows with the indexes I added, but they could also be solved horribly.
    The Session.userID index is a non clustered, non Unique index.
    The query "SELECT ID FROM Users WHERE CompanyID=@CompanyID" will retrieve no more than ten distinct rows.
    Other indexes in sessions:
    UserID-ID-StateID:NonClustered,NonUniqueStateID-ID-UserID:NonClustered,NonUnique
    StateID-PhoneID-UserID:NonClustered,Nonunique
    UserID-PhoneID-PhoneID-ID-LoginTime-LogoutTime-LogoutType-PreAssignedCall:NonClustered,NonUnique
    StateID-ID-PhoneID-UserIDLoginTime-LogoutTime-LogoutType-PreAssignedCall:NonClustered,NonUnique
    I will try deploying the query Scott suggested, to see how it works. The last time I recoded some queries to make them run faster, the bottleneck moved to another query.That was weird. But it seems that for some problematic queries you need to be very aware of how SQL Server tries to work with them, so I´ll try to study some more about it.
    THANK YOU ALL... You have been all so kind!
    Zim
  8. zimzum New Member

    Fine, I can see that I had not posted the results on my investigations. Shame on me.
    Well. Sql Tunning Advisor (and indexes as well) is not good enought when dealing with performance issues. If you have one or two queries that are giving you problems and indexes seem not to work, you have to use the execution planner.
    I got the execution planner, analized each problematic query, step by step and found what was making it so slow. Luckily other queries didn't become slower as I had seen in previous ocassions.
    Ok. thanks a lot. You have been so cool
    Zim

Share This Page