Hello fellow DBAs
I have a strange situation here. I am executing a SQL query which runs for ever till it fills up all the available temp space.
The same query runs within 1 minute in another database on another server. That database is a development database but with same records (and data).
I tried the following:
UPDATE STATISTICS
DBREINDEX
FIXED FRAGMENTATION BY RUNNING DBINDEXDEFRAG
Nothing helps... what should I do next?post the ddl + the query so we can see. Its common to have a rogue/running away query that would take the server down to its knee.
e.g.
select *
from master..syscolumns,master..syscolumns,master..sysc olumns,master..syscolumns
Showing posts with label executing. Show all posts
Showing posts with label executing. Show all posts
Friday, March 30, 2012
Friday, March 23, 2012
Query using mathematical function of values from 2 tables has a performance prob
When I am executing a query that uses a mathematical function on values from 2 tables the query takes much longer than the same query that uses values from 1 table, even though the join remains the same.
Why is this happening?
Is there a way to bypass this problem?
Long query ( values from 2 tables ) :
SELECT
MAX ( ( SIGN ( attribute.keyValue- ( -2027587559 ) ) *SIGN ( attribute.keyValue- ( -2027587559 ) ) -1 ) *-1*data.val ) AS maxVal
FROM
DATA data,
ATTR attribute,
TREE_ELEMENT elm,
TREE_ELEMENT subject
WHERE
data.elmId=elm.id
AND attribute.keyValue IN ( 345647222,1569153803,1569146115,-2027587559 )
AND subject.id=elm.subjectId
AND subject.name = test
Short query ( values from 1 table ) :
SELECT
MAX ( ( SIGN ( data.keyValue- ( -2027587559 ) ) *SIGN ( data.keyValue- ( -2027587559 ) ) -1 ) *-1*data.val ) AS maxVal
FROM
DATA data,
ATTR attribute,
TREE_ELEMENT elm,
TREE_ELEMENT subject
WHERE
data.elmId=elm.id
AND attribute.keyValue IN ( 345647222,1569153803,1569146115,-2027587559 )
AND subject.id=elm.subjectId
AND subject.name = test
Long query execution plan:
Execution Tree
-----
Stream Aggregate ( DEFINE: ( [Expr1004]=MAX ( ( sign ( [attribute].[keyValue]--2027587559 ) *sign ( [attribute].[keyValue]--2027587559 ) -1 ) * ( -1*[data].[val] ) ) ) )
|--Nested Loops ( Inner Join )
|--Hash Match ( Inner Join, HASH: ( [elm].[id] ) = ( [data].[elmId] ) , RESIDUAL: ( [data].[elmId]=[elm].[id] ) )
| |--Nested Loops ( Inner Join, OUTER REFERENCES: ( [subject].[id] ) )
| | |--Index Seek ( OBJECT: ( [TREE_ELEMENT].[TREE_ELEMENT_NAME_IDX] AS [subject] ) ,
SEEK: ( [subject].[name]=test ) ORDERED FORWARD )
| | |--Index Seek ( OBJECT: ( [TREE_ELEMENT].[TREE_ELEMENT_APP_ID_IDX] AS [elm] ) ,
SEEK: ( [elm].[subjectId]=[subject].[id] ) ORDERED FORWARD )
| |--Clustered Index Scan ( OBJECT: ( [DATA].[PK__DATAS_SAMPL__485B9C89] AS [data] ) )
|--Table Spool
|--Index Seek ( OBJECT: ( [ATTR].[TREE_Z_IDX] AS [attribute] ) ,
SEEK: ( [attribute].[keyValue]=-2027587559 OR [attribute].[keyValue]=345647222 OR [attribute].[keyValue]=1569146115 OR [attribute].[keyValue]=1569153803 ) ORDERED FORWARD )
Short query execution plan:
Execution Tree
-----
Stream Aggregate ( DEFINE: ( [Expr1004]=MAX ( [partialagg1005] ) ) )
|--Nested Loops ( Inner Join )
|--Stream Aggregate ( DEFINE: ( [partialagg1005]=MAX ( ( sign ( [data].[keyValue]--2027587559 ) *sign ( [data].[keyValue]--2027587559 ) -1 ) * ( -1*[data].[val] ) ) ) )
| |--Hash Match ( Inner Join, HASH: ( [elm].[id] ) = ( [data].[elmId] ) , RESIDUAL: ( [data].[elmId]=[elm].[id] ) )
| |--Nested Loops ( Inner Join, OUTER REFERENCES: ( [subject].[id] ) )
| | |--Index Seek ( OBJECT: ( [TREE_ELEMENT].[TREE_ELEMENT_NAME_IDX] AS [subject] ) ,
SEEK: ( [subject].[name]=test ) ORDERED FORWARD )
| | |--Index Seek ( OBJECT: ( [TREE_ELEMENT].[TREE_ELEMENT_APP_ID_IDX] AS [elm] ) ,
SEEK: ( [elm].[subjectId]=[subject].[id] ) ORDERED FORWARD )
| |--Clustered Index Scan ( OBJECT: ( [DATA].[PK__DATAS_SAMPL__485B9C89] AS [data] ) )
|--Index Seek ( OBJECT: ( [ATTR].[TREE_Z_IDX] AS [attribute] ) ,
SEEK: ( [attribute].[keyValue]=-2027587559 OR [attribute].[keyValue]=345647222 OR [attribute].[keyValue]=1569146115 OR [attribute].[keyValue]=1569153803 ) ORDERED FORWARD )Just a quick comment:
I don't actually see a(ny) join(s) - instead I see you using WHERE clauses; which is not advised!
The execution plan is assuming INNER JOINS which might not be what you want either.
Why is this happening?
Is there a way to bypass this problem?
Long query ( values from 2 tables ) :
SELECT
MAX ( ( SIGN ( attribute.keyValue- ( -2027587559 ) ) *SIGN ( attribute.keyValue- ( -2027587559 ) ) -1 ) *-1*data.val ) AS maxVal
FROM
DATA data,
ATTR attribute,
TREE_ELEMENT elm,
TREE_ELEMENT subject
WHERE
data.elmId=elm.id
AND attribute.keyValue IN ( 345647222,1569153803,1569146115,-2027587559 )
AND subject.id=elm.subjectId
AND subject.name = test
Short query ( values from 1 table ) :
SELECT
MAX ( ( SIGN ( data.keyValue- ( -2027587559 ) ) *SIGN ( data.keyValue- ( -2027587559 ) ) -1 ) *-1*data.val ) AS maxVal
FROM
DATA data,
ATTR attribute,
TREE_ELEMENT elm,
TREE_ELEMENT subject
WHERE
data.elmId=elm.id
AND attribute.keyValue IN ( 345647222,1569153803,1569146115,-2027587559 )
AND subject.id=elm.subjectId
AND subject.name = test
Long query execution plan:
Execution Tree
-----
Stream Aggregate ( DEFINE: ( [Expr1004]=MAX ( ( sign ( [attribute].[keyValue]--2027587559 ) *sign ( [attribute].[keyValue]--2027587559 ) -1 ) * ( -1*[data].[val] ) ) ) )
|--Nested Loops ( Inner Join )
|--Hash Match ( Inner Join, HASH: ( [elm].[id] ) = ( [data].[elmId] ) , RESIDUAL: ( [data].[elmId]=[elm].[id] ) )
| |--Nested Loops ( Inner Join, OUTER REFERENCES: ( [subject].[id] ) )
| | |--Index Seek ( OBJECT: ( [TREE_ELEMENT].[TREE_ELEMENT_NAME_IDX] AS [subject] ) ,
SEEK: ( [subject].[name]=test ) ORDERED FORWARD )
| | |--Index Seek ( OBJECT: ( [TREE_ELEMENT].[TREE_ELEMENT_APP_ID_IDX] AS [elm] ) ,
SEEK: ( [elm].[subjectId]=[subject].[id] ) ORDERED FORWARD )
| |--Clustered Index Scan ( OBJECT: ( [DATA].[PK__DATAS_SAMPL__485B9C89] AS [data] ) )
|--Table Spool
|--Index Seek ( OBJECT: ( [ATTR].[TREE_Z_IDX] AS [attribute] ) ,
SEEK: ( [attribute].[keyValue]=-2027587559 OR [attribute].[keyValue]=345647222 OR [attribute].[keyValue]=1569146115 OR [attribute].[keyValue]=1569153803 ) ORDERED FORWARD )
Short query execution plan:
Execution Tree
-----
Stream Aggregate ( DEFINE: ( [Expr1004]=MAX ( [partialagg1005] ) ) )
|--Nested Loops ( Inner Join )
|--Stream Aggregate ( DEFINE: ( [partialagg1005]=MAX ( ( sign ( [data].[keyValue]--2027587559 ) *sign ( [data].[keyValue]--2027587559 ) -1 ) * ( -1*[data].[val] ) ) ) )
| |--Hash Match ( Inner Join, HASH: ( [elm].[id] ) = ( [data].[elmId] ) , RESIDUAL: ( [data].[elmId]=[elm].[id] ) )
| |--Nested Loops ( Inner Join, OUTER REFERENCES: ( [subject].[id] ) )
| | |--Index Seek ( OBJECT: ( [TREE_ELEMENT].[TREE_ELEMENT_NAME_IDX] AS [subject] ) ,
SEEK: ( [subject].[name]=test ) ORDERED FORWARD )
| | |--Index Seek ( OBJECT: ( [TREE_ELEMENT].[TREE_ELEMENT_APP_ID_IDX] AS [elm] ) ,
SEEK: ( [elm].[subjectId]=[subject].[id] ) ORDERED FORWARD )
| |--Clustered Index Scan ( OBJECT: ( [DATA].[PK__DATAS_SAMPL__485B9C89] AS [data] ) )
|--Index Seek ( OBJECT: ( [ATTR].[TREE_Z_IDX] AS [attribute] ) ,
SEEK: ( [attribute].[keyValue]=-2027587559 OR [attribute].[keyValue]=345647222 OR [attribute].[keyValue]=1569146115 OR [attribute].[keyValue]=1569153803 ) ORDERED FORWARD )Just a quick comment:
I don't actually see a(ny) join(s) - instead I see you using WHERE clauses; which is not advised!
The execution plan is assuming INNER JOINS which might not be what you want either.
Wednesday, March 21, 2012
query to view current executing jobs
... I know i have asked this before and the response i got is run
sp_help_job.. Please bear with me as Im not a SQL guru . I would like to run
a script in QA and the output should give me the list of jobs that are
currently running. I have around 100 SQL Agent jobs on a server and instead
of refreshing my screen in EM to see the status of running, I want to see
those jobs only from within QA.
Can someone provide that query for me ? Would be highly appreciated.The procedure call is: exec msdb..sp_help_job
For each job, check the coding of current_execution_status:
0 Returns only those jobs that are not idle or suspended.
1 Executing.
2 Waiting for thread.
3 Between retries.
4 Idle.
5 Suspended.
7 Performing completion actions.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uO370WdGFHA.2784@.TK2MSFTNGP10.phx.gbl...
> .. I know i have asked this before and the response i got is run
> sp_help_job.. Please bear with me as Im not a SQL guru . I would like to
run
> a script in QA and the output should give me the list of jobs that are
> currently running. I have around 100 SQL Agent jobs on a server and
instead
> of refreshing my screen in EM to see the status of running, I want to see
> those jobs only from within QA.
> Can someone provide that query for me ? Would be highly appreciated.
>|||You could try the following query, instead of returning all the jobs, it
just returns current active jobs.
--find Jobs that are currently running:
exec msdb..sp_get_composite_job_info @.enabled=1 , @.execution_status = 1
"JohnnyAppleseed" <someone@.microsoft.com> wrote in message
news:uI581odGFHA.3376@.TK2MSFTNGP14.phx.gbl...
> The procedure call is: exec msdb..sp_help_job
> For each job, check the coding of current_execution_status:
> 0 Returns only those jobs that are not idle or suspended.
> 1 Executing.
> 2 Waiting for thread.
> 3 Between retries.
> 4 Idle.
> 5 Suspended.
> 7 Performing completion actions.
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:uO370WdGFHA.2784@.TK2MSFTNGP10.phx.gbl...
> run
> instead
see
>|||Thanks Britney
Do you know what I can use to find just failed jobs ?
"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:ObkP23eGFHA.584@.TK2MSFTNGP14.phx.gbl...
> You could try the following query, instead of returning all the jobs, it
> just returns current active jobs.
>
> --find Jobs that are currently running:
>
> exec msdb..sp_get_composite_job_info @.enabled=1 , @.execution_status = 1
>
>
> "JohnnyAppleseed" <someone@.microsoft.com> wrote in message
> news:uI581odGFHA.3376@.TK2MSFTNGP14.phx.gbl...
to
> see
>|||This is the same too right
exec msdb..sp_help_job @.enabled=1 , @.execution_status = 1
"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:ObkP23eGFHA.584@.TK2MSFTNGP14.phx.gbl...
> You could try the following query, instead of returning all the jobs, it
> just returns current active jobs.
>
> --find Jobs that are currently running:
>
> exec msdb..sp_get_composite_job_info @.enabled=1 , @.execution_status = 1
>
>
> "JohnnyAppleseed" <someone@.microsoft.com> wrote in message
> news:uI581odGFHA.3376@.TK2MSFTNGP14.phx.gbl...
to
> see
>
sp_help_job.. Please bear with me as Im not a SQL guru . I would like to run
a script in QA and the output should give me the list of jobs that are
currently running. I have around 100 SQL Agent jobs on a server and instead
of refreshing my screen in EM to see the status of running, I want to see
those jobs only from within QA.
Can someone provide that query for me ? Would be highly appreciated.The procedure call is: exec msdb..sp_help_job
For each job, check the coding of current_execution_status:
0 Returns only those jobs that are not idle or suspended.
1 Executing.
2 Waiting for thread.
3 Between retries.
4 Idle.
5 Suspended.
7 Performing completion actions.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uO370WdGFHA.2784@.TK2MSFTNGP10.phx.gbl...
> .. I know i have asked this before and the response i got is run
> sp_help_job.. Please bear with me as Im not a SQL guru . I would like to
run
> a script in QA and the output should give me the list of jobs that are
> currently running. I have around 100 SQL Agent jobs on a server and
instead
> of refreshing my screen in EM to see the status of running, I want to see
> those jobs only from within QA.
> Can someone provide that query for me ? Would be highly appreciated.
>|||You could try the following query, instead of returning all the jobs, it
just returns current active jobs.
--find Jobs that are currently running:
exec msdb..sp_get_composite_job_info @.enabled=1 , @.execution_status = 1
"JohnnyAppleseed" <someone@.microsoft.com> wrote in message
news:uI581odGFHA.3376@.TK2MSFTNGP14.phx.gbl...
> The procedure call is: exec msdb..sp_help_job
> For each job, check the coding of current_execution_status:
> 0 Returns only those jobs that are not idle or suspended.
> 1 Executing.
> 2 Waiting for thread.
> 3 Between retries.
> 4 Idle.
> 5 Suspended.
> 7 Performing completion actions.
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:uO370WdGFHA.2784@.TK2MSFTNGP10.phx.gbl...
> run
> instead
see
>|||Thanks Britney
Do you know what I can use to find just failed jobs ?
"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:ObkP23eGFHA.584@.TK2MSFTNGP14.phx.gbl...
> You could try the following query, instead of returning all the jobs, it
> just returns current active jobs.
>
> --find Jobs that are currently running:
>
> exec msdb..sp_get_composite_job_info @.enabled=1 , @.execution_status = 1
>
>
> "JohnnyAppleseed" <someone@.microsoft.com> wrote in message
> news:uI581odGFHA.3376@.TK2MSFTNGP14.phx.gbl...
to
> see
>|||This is the same too right
exec msdb..sp_help_job @.enabled=1 , @.execution_status = 1
"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:ObkP23eGFHA.584@.TK2MSFTNGP14.phx.gbl...
> You could try the following query, instead of returning all the jobs, it
> just returns current active jobs.
>
> --find Jobs that are currently running:
>
> exec msdb..sp_get_composite_job_info @.enabled=1 , @.execution_status = 1
>
>
> "JohnnyAppleseed" <someone@.microsoft.com> wrote in message
> news:uI581odGFHA.3376@.TK2MSFTNGP14.phx.gbl...
to
> see
>
Friday, March 9, 2012
Query to find out actual database size.?
Hi,
I would like to know that actual database size.
For eg.. I have a database DB1 on executing sp_databases,
the size I get is 50GB. But 40GB is actually used and rest
is free.
What query should I give to get actual database size.'
Thanks,
Sid.If you do an sp_spaceused you can see the unuesed and used space, so just
subtract the 2
Dylan
"Sid" <sid00@.rediffmail.com> wrote in message
news:016001c38ef7$a3cea100$a001280a@.phx.gbl...
> Hi,
> I would like to know that actual database size.
> For eg.. I have a database DB1 on executing sp_databases,
> the size I get is 50GB. But 40GB is actually used and rest
> is free.
> What query should I give to get actual database size.'
> Thanks,
> Sid.|||Thanks for the help.
Sid.
>--Original Message--
>If you do an sp_spaceused you can see the unuesed and
used space, so just
>subtract the 2
>Dylan
>"Sid" <sid00@.rediffmail.com> wrote in message
>news:016001c38ef7$a3cea100$a001280a@.phx.gbl...
>> Hi,
>> I would like to know that actual database size.
>> For eg.. I have a database DB1 on executing
sp_databases,
>> the size I get is 50GB. But 40GB is actually used and
rest
>> is free.
>> What query should I give to get actual database size.'
>> Thanks,
>> Sid.
>
>.
>
I would like to know that actual database size.
For eg.. I have a database DB1 on executing sp_databases,
the size I get is 50GB. But 40GB is actually used and rest
is free.
What query should I give to get actual database size.'
Thanks,
Sid.If you do an sp_spaceused you can see the unuesed and used space, so just
subtract the 2
Dylan
"Sid" <sid00@.rediffmail.com> wrote in message
news:016001c38ef7$a3cea100$a001280a@.phx.gbl...
> Hi,
> I would like to know that actual database size.
> For eg.. I have a database DB1 on executing sp_databases,
> the size I get is 50GB. But 40GB is actually used and rest
> is free.
> What query should I give to get actual database size.'
> Thanks,
> Sid.|||Thanks for the help.
Sid.
>--Original Message--
>If you do an sp_spaceused you can see the unuesed and
used space, so just
>subtract the 2
>Dylan
>"Sid" <sid00@.rediffmail.com> wrote in message
>news:016001c38ef7$a3cea100$a001280a@.phx.gbl...
>> Hi,
>> I would like to know that actual database size.
>> For eg.. I have a database DB1 on executing
sp_databases,
>> the size I get is 50GB. But 40GB is actually used and
rest
>> is free.
>> What query should I give to get actual database size.'
>> Thanks,
>> Sid.
>
>.
>
Wednesday, March 7, 2012
Query to check current executing SQL Agent jobs
Is there a query I can run to find current running SQL Agent jobs ?> Is there a query I can run to find current running SQL Agent jobs ?
Use sp_help_job, check the current_execution_status column in the output.
Statuses are not described in Books OnLine; you can find the description at
http://www.win2000mag.com/SQLServer...588/42588.html.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
Use sp_help_job, check the current_execution_status column in the output.
Statuses are not described in Books OnLine; you can find the description at
http://www.win2000mag.com/SQLServer...588/42588.html.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
Query to check current executing SQL Agent jobs
Is there a query I can run to find current running SQL Agent jobs ?
> Is there a query I can run to find current running SQL Agent jobs ?
Use sp_help_job, check the current_execution_status column in the output.
Statuses are not described in Books OnLine; you can find the description at
http://www.win2000mag.com/SQLServer/...88/42588.html.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
> Is there a query I can run to find current running SQL Agent jobs ?
Use sp_help_job, check the current_execution_status column in the output.
Statuses are not described in Books OnLine; you can find the description at
http://www.win2000mag.com/SQLServer/...88/42588.html.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
Query to check current executing SQL Agent jobs
Is there a query I can run to find current running SQL Agent jobs ?> Is there a query I can run to find current running SQL Agent jobs ?
Use sp_help_job, check the current_execution_status column in the output.
Statuses are not described in Books OnLine; you can find the description at
http://www.win2000mag.com/SQLServer...588/42588.html.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
Use sp_help_job, check the current_execution_status column in the output.
Statuses are not described in Books OnLine; you can find the description at
http://www.win2000mag.com/SQLServer...588/42588.html.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
Query to check current executing SQL Agent jobs
Is there a query I can run to find current running SQL Agent jobs ?> Is there a query I can run to find current running SQL Agent jobs ?
Use sp_help_job, check the current_execution_status column in the output.
Statuses are not described in Books OnLine; you can find the description at
http://www.win2000mag.com/SQLServer/Article/ArticleID/42588/42588.html.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
Use sp_help_job, check the current_execution_status column in the output.
Statuses are not described in Books OnLine; you can find the description at
http://www.win2000mag.com/SQLServer/Article/ArticleID/42588/42588.html.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
Saturday, February 25, 2012
Query Time Limit?
I know I can use SET QUERY_GOVERNOR_COST_LIMIT to prevent a query from executing if its estimated execution time exceeds the value of the specified time in seconds, but is there some way to have a query stop executing (and raise an error) if the time exceeds a specified time? For example, if the query takes longer than 20 seconds, have it stop executing and raise an error?
Thanks,
-Dave
It prevents query from executing based on the cost rather than controlling resources.
BOL
'If you specify a nonzero, nonnegative value, the query governor disallows execution of any query that has an estimated cost exceeding that value. Specifying 0 (the default) for this option turns off the query governor, and all queries are allowed to run indefinitely.
"Query cost" refers to the estimated elapsed time, in seconds, required to complete a query on a specific hardware configuration.
|||If you are referring to having the ability to control any one specific query -that is not possible.
QUERY_GOVERNOR_COST_LIMIT effects ALL queries executed on the server. It prevents a query from executing IF the estimated time exceeds the limit. Say the QUERY_GOVERNOR_COST_LIMIT was 120 seconds, the estimated plan was 119 seconds -the query would then execute EVEN if the resulting time required exceeded 120 seconds. As far as I am aware, there is no way to have an executing query abort at some predetemined elapsed time value.
Of course, from an application, it might be possible to spawn a thread that executed a query against SQL Server, and then the parent thread could abort the executing thread after a predetermined time. But that could be messy...
Query Time in SQL Server
I am using SQL Server and ASP.NET. I am executing a couple of stored procedures and displaying the results in a datagrid. Since these Stored procedures takes around 2-4 minutes each, I want to display a status bar on the web by displaying the approximate time the user needs to wait before seeing the results.
My question is: Is there a way to find out the approximate EXECUTION TIME of the stored procedure before hand. Also, if that is possible, how do i access the same from the ASP.NET code..
Thanks
SathyaI do not know of a way to determine the approximate execution time of a stored procedure before it runs.
Perhaps you should use the worst-case execution time as your estimate for each?
And also, 2-4 minutes for a query to run is not reasonable. You should strongly consider spending some time trying to optimize these queries.
Terri|||I agree with Terri on this one. 2-4 minutes for a query to run especially as a stored procedure is saying a tremendous amount to the inefficiencies you may have in your design or execution.
Initially, I'd take the time to repair that before continuing further to make your future tasks with your application even more complicated.
Subscribe to:
Posts (Atom)