none
a simple SQL Server Syntax cause a hight CPU utlization to 100%

    Question

  • Hi all ,

    I have SQL Server syntax (very simple as I guess) but running continuously and causing that the SQL CPU utilization always running under 100% one more problem that the code isn't encapsulated in stored procedure and run directly from the application (windows services)

    SQL Server is 2008 x64 clustered

    SELECT TOP  1 * from [Tbl_001]
                        where STATUS =0
                         and ID =200
                         order by ID
    ---if the result = true then 
    
    
    update [Tbl_001] set STATUS=20 where ID=12345
    please do you have any suggestion how can I optimize this ?


    • Edited by SQL Kitchen Sunday, October 20, 2013 8:03 AM
    Sunday, October 20, 2013 8:02 AM

Answers

  • Now everything makes sense. It explains why the clustered index isn't seeked. It explains why you needed the "TOP 1" and the "ORDER BY ID". And it explains the query plan.

    Your view's definition shows that your data model is not properly normalized. Because you are (apparently) storing a Comp_ID as part of the FILE_NAME column.

    My advice is to store Comp_ID as its own column. Also, if FILE_NAME is currently the combination of an ID and an actual file name, then I would strip the ID's from the FILE_NAMEs.

    After that, you can simply add an index on Comp_ID, and your query will probably be very fast.

    If normalizing your table is not an option, then you could consider adding Comp_ID as a persisted computed column, and then index that.


    Gert-Jan

    Monday, October 21, 2013 8:50 PM
  • If the nonclustered index covers your predicates, then there is no need to worry about the key lookups. Since you are selecting the TOP 1 row, in such case there will only be one key lookup.

    If you hover over the Index Seek operator, and if you hover over the Key Lookup operator, for ideal performance, you shouldn't see any "FILTER" or "PREDICATE".

    Also, if you hover over the line to Key Lookup, or the Nested Loops operator should indicate a very low number of estimated rows; should be very close to 1 (or exactly 1).


    Gert-Jan

    Thursday, October 24, 2013 8:22 PM

All replies

  • How huge  is the data in [Tbl_001]? Do you have an index on ID/Status columns?


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, October 20, 2013 8:57 AM
    Answerer
  • Thanks Uri for your reply

    1. number of rows in that table is around 2 million
    2. Id column is primary key column (with default clustered index)
    3. No index on Column STATUS
    Sunday, October 20, 2013 9:18 AM
  • Sounds like your indexes are ideal for this query.

    The query itself is fine too.


    Gert-Jan

    Sunday, October 20, 2013 9:36 AM
  • that's what made me wondering how such CPU utilization could be happened :),

    also once stop that windows service fire above code the CPU back dramatically to the normal state , ???!!! 


    • Edited by SQL Kitchen Sunday, October 20, 2013 9:59 AM
    Sunday, October 20, 2013 9:57 AM
  • You could check whether there are any triggers on the table. Maybe there is an inefficient update trigger active?

    Also, you may want to check the query plan, just to make sure it is actually seeking the clustered index.

    And finally, it depends on the frequency with which this ad-hoc query is run. How many times per second is it used?


    Gert-Jan

    Sunday, October 20, 2013 10:05 AM
  • in the select query:
    - If you are using Filter (where) on ID=200, then why do you need order by ID?
    - if this is unique value then why do you need top 1, as there is only 1 record with ID=200?

    * to add to what Gert wrote, The update query need to use X lock. make sure that you do not lock the entire table, but only the row, AND check the dm_tran_locks and dm_os_wait_stats to make sure that the update is not lock / waiting for some other query.


    [Personal Site] [Blog] [Facebook]signature

    Sunday, October 20, 2013 10:30 AM
  • You could check whether there are any triggers on the table. Maybe there is an inefficient update trigger active?

    Also, you may want to check the query plan, just to make sure it is actually seeking the clustered index.

    And finally, it depends on the frequency with which this ad-hoc query is run. How many times per second is it used?


    Gert-Jan

    1. There is no triggers in place
    2. The execution plan is seems good as show below
    3. this query run scheduled to run every 1 sec.

     

    Sunday, October 20, 2013 11:36 AM
  • Make the T-SQL script into a stored procedure and let us know what happens.

    Also for quick help post the entire code and related data/index DDL.


    Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012



    Sunday, October 20, 2013 11:38 AM
    Moderator
  • Did you read what I wrote you before?

    In addition to what I wrote earlier, and given execution plan, I recommend you to try using "OPTION (MAXDOP 1)" to limit the use of CPU and to disable the use of parallel. tell us the result...



    [Personal Site] [Blog] [Facebook]signature

    Sunday, October 20, 2013 12:40 PM
  • Hmm IS is unique column if you remove status column from WHERE condition , do you see CI Seek? Why it does a scanning?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, October 20, 2013 1:07 PM
    Answerer
  • The execution plan is seems good as show below

    The query plan of the UPDATE statement is fine, just as expected.

    The query plan of the SELECT statement is complete the wrong choice, not OK at all. It is scanning the clustered index instead of seeking it.

    Are you sure that the Primary Key constraint is declared on the ID column? Did you post the actual query?

    Because it doesn't add up. Either there is no unique index on the ID column, or the ID column is not a SMALLINT, INT or BIGINT, or there is some other data type mismatch going on.


    Gert-Jan

    Sunday, October 20, 2013 3:50 PM

  • one more thing I call this table through view to substring some string values in a different named column
    • Edited by SQL Kitchen Sunday, October 20, 2013 4:06 PM
    Sunday, October 20, 2013 4:03 PM
  • Is this [Tbl_001] a VIEW?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, October 21, 2013 4:37 AM
    Answerer
  • No actually the [Tbl_001] is a table but I am call a view contains only that table (as mentioned to do some column order and column format before retrieve data)  

    here is how the syntax look like in the view

    SELECT
    ID, NAME, FILE_PATH, STATUS, CREATION_DATE, Type, USER_ID,
     CAST(SUBSTRING(NAME, 0, CHARINDEX('_', NAME)) AS TINYINT) AS 'Comp_ID'
    FROM Tbl_001

    still waiting your input :)

    Regtards


    • Edited by SQL Kitchen Monday, October 21, 2013 6:43 AM
    Monday, October 21, 2013 6:36 AM
  • The execution plan is seems good as show below

    The query plan of the UPDATE statement is fine, just as expected.

    The query plan of the SELECT statement is complete the wrong choice, not OK at all. It is scanning the clustered index instead of seeking it.

    Are you sure that the Primary Key constraint is declared on the ID column? Did you post the actual query?

    Because it doesn't add up. Either there is no unique index on the ID column, or the ID column is not a SMALLINT, INT or BIGINT, or there is some other data type mismatch going on.


    Gert-Jan

    Hello Gert,

    I have applied more indexes and generate a new executaion plan as shown below , it seems fine now , what do you think ?

    Monday, October 21, 2013 10:14 AM
  • The query plan is better, but it is not fine. Because this query plan still requires a complete index to be scanned (even though this index will be much smaller than the clustered index).

    Can you please post the query where you select from the view? Because I am worried that you are not filtering on ID, but on Comp_ID (which would make the query completely different).

    When it comes to performance related questions, it is very important to always give the complete query, because addition columns, joins or predicates can have a huge impact.


    Gert-Jan

    Monday, October 21, 2013 12:46 PM
  • Hello Gert,

    Here is the SELECT in the view and it's execution plan

    SELECT
    ID, FILE_NAME, FILE_PATH, STATUS, CREATION_DATE, Type, USER_ID,
     CAST(SUBSTRING(FILE_NAME, 0, CHARINDEX('_', FILE_NAME)) AS TINYINT) AS 'Comp_ID'
    FROM Tbl_001


    The Select call above view and written in the application and it's execution plan

    SELECT TOP  1 * from [tbl_001_VIEW]
                        where STATUS =0
                         and Comp_ID =40
                         order by ID

    • Edited by SQL Kitchen Monday, October 21, 2013 1:44 PM
    Monday, October 21, 2013 1:36 PM
  • Now everything makes sense. It explains why the clustered index isn't seeked. It explains why you needed the "TOP 1" and the "ORDER BY ID". And it explains the query plan.

    Your view's definition shows that your data model is not properly normalized. Because you are (apparently) storing a Comp_ID as part of the FILE_NAME column.

    My advice is to store Comp_ID as its own column. Also, if FILE_NAME is currently the combination of an ID and an actual file name, then I would strip the ID's from the FILE_NAMEs.

    After that, you can simply add an index on Comp_ID, and your query will probably be very fast.

    If normalizing your table is not an option, then you could consider adding Comp_ID as a persisted computed column, and then index that.


    Gert-Jan

    Monday, October 21, 2013 8:50 PM
  • Thanks Gert for your valuable replies ,

    I am going to follow your advice and inform you back with the status,

    Thanks

     

    Tuesday, October 22, 2013 8:52 AM
  • Hello Gert , I made my SQL syntax direct to the table without using the view and the execution plan changed as following I think it's now fine :)except the key lookups from the query,

    do you have any idea how I can eliminate the key lookup.... (covering) index ??!!


    • Edited by SQL Kitchen Wednesday, October 23, 2013 9:44 AM
    Wednesday, October 23, 2013 9:43 AM
  • If the nonclustered index covers your predicates, then there is no need to worry about the key lookups. Since you are selecting the TOP 1 row, in such case there will only be one key lookup.

    If you hover over the Index Seek operator, and if you hover over the Key Lookup operator, for ideal performance, you shouldn't see any "FILTER" or "PREDICATE".

    Also, if you hover over the line to Key Lookup, or the Nested Loops operator should indicate a very low number of estimated rows; should be very close to 1 (or exactly 1).


    Gert-Jan

    Thursday, October 24, 2013 8:22 PM