query assistance: hangs

Showing posts with label hangs. Show all posts

Wednesday, March 28, 2012

Query Hangs/Time out

I have an intermittent problem that I have not been able to fix.
I have a database that will all of a sudden start timing out on a particular
query that uses both stored procedures and Views on an aspx page. If I
perform the same query in Query Analyzer it will just hang...I've let it go
10 minutes and no result.
I looked for blocked processes when this occurs - there are none. I've
checked the error log, nothing.
To correct this problem, I've found that if I simply copy the database to a
devel box the query on the prod server will again work fine - then we can go
several hours to several days before it starts hanging again.
Now, if I try to put the prod database in single user, then detach, then
re-attach, the query still hangs.
I'm assuming a reboot of the server itself would do the trick too...but
avoiding that for obvious reasons.
We have not run into this on any of our other databases on either the
production server or our development desktops. We have not been able to
recreate this condition on a test system.
The devel/test boxes are xp pro..latest service paks.
The production server is win2003..latest service paks too.
We are on SQL2000 SP4 on all systems.
Has anyone heard of this? Any help would be appreciated.Reid,
I would suggest tunning the query(s). Generate a query plan and concentrate
on the steps that take up most of the % of the execution plan. Add indexes
where identified typically columns used in the where clause are a good place
to start and see if that helps. If there are indexes already on the table(s)
and or view(s) then I would suggest a DBCC reindex to reindex the indexes so
they will preform better. I had the exact same issue with the aspx page that
went into production and ran fine for 3 months and they hung. I added an
index or two and it started running in less than a 1 second so indexes are a
wonderful thing.
Hope this helps,
Rick Butler MCP
"Reid" wrote:
> I have an intermittent problem that I have not been able to fix.
> I have a database that will all of a sudden start timing out on a particular
> query that uses both stored procedures and Views on an aspx page. If I
> perform the same query in Query Analyzer it will just hang...I've let it go
> 10 minutes and no result.
> I looked for blocked processes when this occurs - there are none. I've
> checked the error log, nothing.
> To correct this problem, I've found that if I simply copy the database to a
> devel box the query on the prod server will again work fine - then we can go
> several hours to several days before it starts hanging again.
> Now, if I try to put the prod database in single user, then detach, then
> re-attach, the query still hangs.
> I'm assuming a reboot of the server itself would do the trick too...but
> avoiding that for obvious reasons.
> We have not run into this on any of our other databases on either the
> production server or our development desktops. We have not been able to
> recreate this condition on a test system.
> The devel/test boxes are xp pro..latest service paks.
> The production server is win2003..latest service paks too.
> We are on SQL2000 SP4 on all systems.
> Has anyone heard of this? Any help would be appreciated.|||RcsterMan,
Thanks for the suggestions. I've actually done all of that...they where
the first things I tried. This situation is intermittent...if I was having
tuning issues would I not be having more consistant hangs? After I do the DB
copy, or reboot the server, the site runs fine and runs the querys in several
seconds - which is quite acceptable due to the complexity of the query. But
then it simply hangs - the performance doesn't degrade, it simply hangs -
While I can do other searches/query's with no issues.
I knew this would be a very nebulas problem to bug out..any other suggestions?
Thanks in advance for your time,
Reid
"RicsterMan" wrote:
> Reid,
> I would suggest tunning the query(s). Generate a query plan and concentrate
> on the steps that take up most of the % of the execution plan. Add indexes
> where identified typically columns used in the where clause are a good place
> to start and see if that helps. If there are indexes already on the table(s)
> and or view(s) then I would suggest a DBCC reindex to reindex the indexes so
> they will preform better. I had the exact same issue with the aspx page that
> went into production and ran fine for 3 months and they hung. I added an
> index or two and it started running in less than a 1 second so indexes are a
> wonderful thing.
> Hope this helps,
> Rick Butler MCP
> "Reid" wrote:
> > I have an intermittent problem that I have not been able to fix.
> >
> > I have a database that will all of a sudden start timing out on a particular
> > query that uses both stored procedures and Views on an aspx page. If I
> > perform the same query in Query Analyzer it will just hang...I've let it go
> > 10 minutes and no result.
> >
> > I looked for blocked processes when this occurs - there are none. I've
> > checked the error log, nothing.
> >
> > To correct this problem, I've found that if I simply copy the database to a
> > devel box the query on the prod server will again work fine - then we can go
> > several hours to several days before it starts hanging again.
> >
> > Now, if I try to put the prod database in single user, then detach, then
> > re-attach, the query still hangs.
> >
> > I'm assuming a reboot of the server itself would do the trick too...but
> > avoiding that for obvious reasons.
> >
> > We have not run into this on any of our other databases on either the
> > production server or our development desktops. We have not been able to
> > recreate this condition on a test system.
> >
> > The devel/test boxes are xp pro..latest service paks.
> >
> > The production server is win2003..latest service paks too.
> >
> > We are on SQL2000 SP4 on all systems.
> >
> > Has anyone heard of this? Any help would be appreciated.|||What is the wait type when the query hangs? Is it doing
anything or just actually just sitting there - did you check
CPU, Memory, IO for the SPID while the query appears to
hang?
What's going on with the locks when this happens? Have you
run profiler to see what else is going on? Did you also
monitor for recompiles, statistics being updated? Did you
check for issues with file size changes during this?
Anything in particular going on with system itself at the
time the process seems to hang?
-Sue
On Mon, 16 Jan 2006 20:56:03 -0800, "Reid"
<Reid@.discussions.microsoft.com> wrote:
>RcsterMan,
>Thanks for the suggestions. I've actually done all of that...they where
>the first things I tried. This situation is intermittent...if I was having
>tuning issues would I not be having more consistant hangs? After I do the DB
>copy, or reboot the server, the site runs fine and runs the querys in several
>seconds - which is quite acceptable due to the complexity of the query. But
>then it simply hangs - the performance doesn't degrade, it simply hangs -
>While I can do other searches/query's with no issues.
>I knew this would be a very nebulas problem to bug out..any other suggestions?
>Thanks in advance for your time,
>Reid
>"RicsterMan" wrote:
>> Reid,
>> I would suggest tunning the query(s). Generate a query plan and concentrate
>> on the steps that take up most of the % of the execution plan. Add indexes
>> where identified typically columns used in the where clause are a good place
>> to start and see if that helps. If there are indexes already on the table(s)
>> and or view(s) then I would suggest a DBCC reindex to reindex the indexes so
>> they will preform better. I had the exact same issue with the aspx page that
>> went into production and ran fine for 3 months and they hung. I added an
>> index or two and it started running in less than a 1 second so indexes are a
>> wonderful thing.
>> Hope this helps,
>> Rick Butler MCP
>> "Reid" wrote:
>> > I have an intermittent problem that I have not been able to fix.
>> >
>> > I have a database that will all of a sudden start timing out on a particular
>> > query that uses both stored procedures and Views on an aspx page. If I
>> > perform the same query in Query Analyzer it will just hang...I've let it go
>> > 10 minutes and no result.
>> >
>> > I looked for blocked processes when this occurs - there are none. I've
>> > checked the error log, nothing.
>> >
>> > To correct this problem, I've found that if I simply copy the database to a
>> > devel box the query on the prod server will again work fine - then we can go
>> > several hours to several days before it starts hanging again.
>> >
>> > Now, if I try to put the prod database in single user, then detach, then
>> > re-attach, the query still hangs.
>> >
>> > I'm assuming a reboot of the server itself would do the trick too...but
>> > avoiding that for obvious reasons.
>> >
>> > We have not run into this on any of our other databases on either the
>> > production server or our development desktops. We have not been able to
>> > recreate this condition on a test system.
>> >
>> > The devel/test boxes are xp pro..latest service paks.
>> >
>> > The production server is win2003..latest service paks too.
>> >
>> > We are on SQL2000 SP4 on all systems.
>> >
>> > Has anyone heard of this? Any help would be appreciated.|||Sue,
I'll need to wait until it starts doing it again. Right now it is working
as it is supposed to. I'll pay attention to the info on the SPID as
suggested.
I have run profiler. In fact, I believe at one time when the query was
hanging I ran the Index tuning wizard and it came back with some suggestions
that I accepted. Tried to re-run the query and it continued to hang. So I
did the copy db again and tried the query and it worked fine.
The rest of the server appears to work fine when this is occuring. I can do
other queries/operations on the site and no problems. It seems to be
isolated to this one db.
I'll post when I have more info..thanks for you time.
"Sue Hoegemeier" wrote:
> What is the wait type when the query hangs? Is it doing
> anything or just actually just sitting there - did you check
> CPU, Memory, IO for the SPID while the query appears to
> hang?
> What's going on with the locks when this happens? Have you
> run profiler to see what else is going on? Did you also
> monitor for recompiles, statistics being updated? Did you
> check for issues with file size changes during this?
> Anything in particular going on with system itself at the
> time the process seems to hang?
> -Sue
> On Mon, 16 Jan 2006 20:56:03 -0800, "Reid"
> <Reid@.discussions.microsoft.com> wrote:
> >RcsterMan,
> >
> >Thanks for the suggestions. I've actually done all of that...they where
> >the first things I tried. This situation is intermittent...if I was having
> >tuning issues would I not be having more consistant hangs? After I do the DB
> >copy, or reboot the server, the site runs fine and runs the querys in several
> >seconds - which is quite acceptable due to the complexity of the query. But
> >then it simply hangs - the performance doesn't degrade, it simply hangs -
> >While I can do other searches/query's with no issues.
> >
> >I knew this would be a very nebulas problem to bug out..any other suggestions?
> >
> >Thanks in advance for your time,
> >Reid
> >
> >"RicsterMan" wrote:
> >
> >> Reid,
> >> I would suggest tunning the query(s). Generate a query plan and concentrate
> >> on the steps that take up most of the % of the execution plan. Add indexes
> >> where identified typically columns used in the where clause are a good place
> >> to start and see if that helps. If there are indexes already on the table(s)
> >> and or view(s) then I would suggest a DBCC reindex to reindex the indexes so
> >> they will preform better. I had the exact same issue with the aspx page that
> >> went into production and ran fine for 3 months and they hung. I added an
> >> index or two and it started running in less than a 1 second so indexes are a
> >> wonderful thing.
> >>
> >> Hope this helps,
> >> Rick Butler MCP
> >>
> >> "Reid" wrote:
> >>
> >> > I have an intermittent problem that I have not been able to fix.
> >> >
> >> > I have a database that will all of a sudden start timing out on a particular
> >> > query that uses both stored procedures and Views on an aspx page. If I
> >> > perform the same query in Query Analyzer it will just hang...I've let it go
> >> > 10 minutes and no result.
> >> >
> >> > I looked for blocked processes when this occurs - there are none. I've
> >> > checked the error log, nothing.
> >> >
> >> > To correct this problem, I've found that if I simply copy the database to a
> >> > devel box the query on the prod server will again work fine - then we can go
> >> > several hours to several days before it starts hanging again.
> >> >
> >> > Now, if I try to put the prod database in single user, then detach, then
> >> > re-attach, the query still hangs.
> >> >
> >> > I'm assuming a reboot of the server itself would do the trick too...but
> >> > avoiding that for obvious reasons.
> >> >
> >> > We have not run into this on any of our other databases on either the
> >> > production server or our development desktops. We have not been able to
> >> > recreate this condition on a test system.
> >> >
> >> > The devel/test boxes are xp pro..latest service paks.
> >> >
> >> > The production server is win2003..latest service paks too.
> >> >
> >> > We are on SQL2000 SP4 on all systems.
> >> >
> >> > Has anyone heard of this? Any help would be appreciated.
>

Query Hangs/Time out

Monday, March 26, 2012

Query Hangs when Parameters are scaled down.

Query Hangs on Values that start with a number

Having a strange thing happen:
Have the following query:
SET NOCOUNT ON
DECLARE @.StartDate DateTime
DECLARE @.EndDate DateTime
SET @.StartDate = DateAdd(dd,-5,GetDate())
SET @.EndDate = GetDate()
SET NOCOUNT OFF
SET ROWCOUNT 0
SELECT SummaryDate = sd.SummaryDate, TagName = sd.TagName, Value =
sd.Value, Duration = sd.Duration, EventTag = sd.EventTag
FROM v_SummaryData sd
INNER JOIN Tag ON Tag.TagName = sd.TagName
WHERE SummaryDate >= @.StartDate
AND SummaryDate <= @.EndDate
AND sd.TagName in ('20SWD','22SWD') AND CalcType = 'AVG' AND
Duration= '86400' ORDER BY SummaryDate, TagName
SET ROWCOUNT 0
If running this query, it hangs for about 9 minutes before returning a
value. If we comment out the INNER JOIN statement, works in 1 second.
Now, here's the really big twist. If we add in a tagname of 'TI74' as
a third tag, query runs right away!
It appears that if there is more then one tagname that starts with a
number, it will hang. As soon as a tagname is present that starts with
a letter, it works great.
We are at a loss as to what could be causing the issue. Have tried re-
indexing the Tag table, but still no resolve.
Running SQL Server 2000 SP3
On Jun 12, 2:54 am, Mini67 <wor...@.gmail.com> wrote:
> Having a strange thing happen:
> Have the following query:
> SET NOCOUNT ON
> DECLARE @.StartDate DateTime
> DECLARE @.EndDate DateTime
> SET @.StartDate = DateAdd(dd,-5,GetDate())
> SET @.EndDate = GetDate()
> SET NOCOUNT OFF
> SET ROWCOUNT 0
> SELECT SummaryDate = sd.SummaryDate, TagName = sd.TagName, Value =
> sd.Value, Duration = sd.Duration, EventTag = sd.EventTag
> FROM v_SummaryData sd
> INNER JOIN Tag ON Tag.TagName = sd.TagName
> WHERE SummaryDate >= @.StartDate
> AND SummaryDate <= @.EndDate
> AND sd.TagName in ('20SWD','22SWD') AND CalcType = 'AVG' AND
> Duration= '86400' ORDER BY SummaryDate, TagName
> SET ROWCOUNT 0
> If running this query, it hangs for about 9 minutes before returning a
> value. If we comment out the INNER JOIN statement, works in 1 second.
> Now, here's the really big twist. If we add in a tagname of 'TI74' as
> a third tag, query runs right away!
> It appears that if there is more then one tagname that starts with a
> number, it will hang. As soon as a tagname is present that starts with
> a letter, it works great.
> We are at a loss as to what could be causing the issue. Have tried re-
> indexing the Tag table, but still no resolve.
> Running SQL Server 2000 SP3
Comments:
1. Why you are doing SET NOCOUNT OFF , SET ROWCOUNT 0
2. Is V_SummaryData View ? If so , table involved is index on
tagname ?
3. Try changing sd.TagName in ('20SWD','22SWD') to
Tag.TagName in ('20SWD','22SWD') and check
4. In the Where clause introduce the tablename , Instead of Duration=
'86400'
sd.Duration= '86400'

Query Hangs on Values that start with a number

Having a strange thing happen:
Have the following query:
SET NOCOUNT ON
DECLARE @.StartDate DateTime
DECLARE @.EndDate DateTime
SET @.StartDate = DateAdd(dd,-5,GetDate())
SET @.EndDate = GetDate()
SET NOCOUNT OFF
SET ROWCOUNT 0
SELECT SummaryDate = sd.SummaryDate, TagName = sd.TagName, Value = sd.Value, Duration = sd.Duration, EventTag = sd.EventTag
FROM v_SummaryData sd
INNER JOIN Tag ON Tag.TagName = sd.TagName
WHERE SummaryDate >= @.StartDate
AND SummaryDate <= @.EndDate
AND sd.TagName in ('20SWD','22SWD') AND CalcType = 'AVG' AND
Duration= '86400' ORDER BY SummaryDate, TagName
SET ROWCOUNT 0
If running this query, it hangs for about 9 minutes before returning a
value. If we comment out the INNER JOIN statement, works in 1 second.
Now, here's the really big twist. If we add in a tagname of 'TI74' as
a third tag, query runs right away!
It appears that if there is more then one tagname that starts with a
number, it will hang. As soon as a tagname is present that starts with
a letter, it works great.
We are at a loss as to what could be causing the issue. Have tried re-
indexing the Tag table, but still no resolve.
Running SQL Server 2000 SP3On Jun 12, 2:54 am, Mini67 <wor...@.gmail.com> wrote:
> Having a strange thing happen:
> Have the following query:
> SET NOCOUNT ON
> DECLARE @.StartDate DateTime
> DECLARE @.EndDate DateTime
> SET @.StartDate = DateAdd(dd,-5,GetDate())
> SET @.EndDate = GetDate()
> SET NOCOUNT OFF
> SET ROWCOUNT 0
> SELECT SummaryDate = sd.SummaryDate, TagName = sd.TagName, Value => sd.Value, Duration = sd.Duration, EventTag = sd.EventTag
> FROM v_SummaryData sd
> INNER JOIN Tag ON Tag.TagName = sd.TagName
> WHERE SummaryDate >= @.StartDate
> AND SummaryDate <= @.EndDate
> AND sd.TagName in ('20SWD','22SWD') AND CalcType = 'AVG' AND
> Duration= '86400' ORDER BY SummaryDate, TagName
> SET ROWCOUNT 0
> If running this query, it hangs for about 9 minutes before returning a
> value. If we comment out the INNER JOIN statement, works in 1 second.
> Now, here's the really big twist. If we add in a tagname of 'TI74' as
> a third tag, query runs right away!
> It appears that if there is more then one tagname that starts with a
> number, it will hang. As soon as a tagname is present that starts with
> a letter, it works great.
> We are at a loss as to what could be causing the issue. Have tried re-
> indexing the Tag table, but still no resolve.
> Running SQL Server 2000 SP3
Comments:
1. Why you are doing SET NOCOUNT OFF , SET ROWCOUNT 0
2. Is V_SummaryData View ? If so , table involved is index on
tagname ?
3. Try changing sd.TagName in ('20SWD','22SWD') to
Tag.TagName in ('20SWD','22SWD') and check
4. In the Where clause introduce the tablename , Instead of Duration='86400'
sd.Duration= '86400'

Query Hangs on Values that start with a number

Having a strange thing happen:
Have the following query:
SET NOCOUNT ON
DECLARE @.StartDate DateTime
DECLARE @.EndDate DateTime
SET @.StartDate = DateAdd(dd,-5,GetDate())
SET @.EndDate = GetDate()
SET NOCOUNT OFF
SET ROWCOUNT 0
SELECT SummaryDate = sd.SummaryDate, TagName = sd.TagName, Value =
sd.Value, Duration = sd.Duration, EventTag = sd.EventTag
FROM v_SummaryData sd
INNER JOIN Tag ON Tag.TagName = sd.TagName
WHERE SummaryDate >= @.StartDate
AND SummaryDate <= @.EndDate
AND sd.TagName in ('20SWD','22SWD') AND CalcType = 'AVG' AND
Duration= '86400' ORDER BY SummaryDate, TagName
SET ROWCOUNT 0
If running this query, it hangs for about 9 minutes before returning a
value. If we comment out the INNER JOIN statement, works in 1 second.
Now, here's the really big twist. If we add in a tagname of 'TI74' as
a third tag, query runs right away!
It appears that if there is more then one tagname that starts with a
number, it will hang. As soon as a tagname is present that starts with
a letter, it works great.
We are at a loss as to what could be causing the issue. Have tried re-
indexing the Tag table, but still no resolve.
Running SQL Server 2000 SP3On Jun 12, 2:54 am, Mini67 <wor...@.gmail.com> wrote:
> Having a strange thing happen:
> Have the following query:
> SET NOCOUNT ON
> DECLARE @.StartDate DateTime
> DECLARE @.EndDate DateTime
> SET @.StartDate = DateAdd(dd,-5,GetDate())
> SET @.EndDate = GetDate()
> SET NOCOUNT OFF
> SET ROWCOUNT 0
> SELECT SummaryDate = sd.SummaryDate, TagName = sd.TagName, Value =
> sd.Value, Duration = sd.Duration, EventTag = sd.EventTag
> FROM v_SummaryData sd
> INNER JOIN Tag ON Tag.TagName = sd.TagName
> WHERE SummaryDate >= @.StartDate
> AND SummaryDate <= @.EndDate
> AND sd.TagName in ('20SWD','22SWD') AND CalcType = 'AVG' AND
> Duration= '86400' ORDER BY SummaryDate, TagName
> SET ROWCOUNT 0
> If running this query, it hangs for about 9 minutes before returning a
> value. If we comment out the INNER JOIN statement, works in 1 second.
> Now, here's the really big twist. If we add in a tagname of 'TI74' as
> a third tag, query runs right away!
> It appears that if there is more then one tagname that starts with a
> number, it will hang. As soon as a tagname is present that starts with
> a letter, it works great.
> We are at a loss as to what could be causing the issue. Have tried re-
> indexing the Tag table, but still no resolve.
> Running SQL Server 2000 SP3
Comments:
1. Why you are doing SET NOCOUNT OFF , SET ROWCOUNT 0
2. Is V_SummaryData View ? If so , table involved is index on
tagname ?
3. Try changing sd.TagName in ('20SWD','22SWD') to
Tag.TagName in ('20SWD','22SWD') and check
4. In the Where clause introduce the tablename , Instead of Duration=
'86400'
sd.Duration= '86400'

Query hangs during a seperate write

When writing 100's of records to 1 or more tables within a transaction, the
server seems to hang any query trying to read from those tables until either
a Timeout or the write process completes. I'm not doing a lock.
The writes and reads are separate applications.
What would cause this?How are you doing the write?
AMB
"Joe" wrote:

> When writing 100's of records to 1 or more tables within a transaction, th
e
> server seems to hang any query trying to read from those tables until eith
er
> a Timeout or the write process completes. I'm not doing a lock.
> The writes and reads are separate applications.
> What would cause this?
>
>|||using ADO.NET. The DataAdaptor is doing Inserts into the tables.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:9960EB96-B274-4A6C-85C0-3BB485E1D411@.microsoft.com...
> How are you doing the write?
>
> AMB
>
> "Joe" wrote:
>
the
either|||Do you know the isolation level being used in the transaction?
You can use Profiler to trace the locks, Lock:Acquired / Lock:Escalation and
Lock:Released.
AMB
"Joe" wrote:

> using ADO.NET. The DataAdaptor is doing Inserts into the tables.
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:9960EB96-B274-4A6C-85C0-3BB485E1D411@.microsoft.com...
> the
> either
>
>|||Use with (nolock) on your select statements, for ex.
SELECT au_lname FROM authors WITH (NOLOCK)
This will ensure that select stmt. does the dirty rather than acquiring
an shared lock while the exclusive lock is on.|||You may have a deadlock, and insert is relying on an ID inserted in another
table which itself is waiting for the hanging insert to finish.
If it happens all the time - simply check your logic does the flow work? -
you not done something silly in a transaction.
Otherwise if its intermitant find what process blocks it and syncronise the
order which both processes use DB tables. Try and minimise the time spent in
transactions.
Use profiler to / or current actvity pane in Enterprise man to explore the
processes
Just an approach I've found useful.

query hangs

Hi,
Have a query that runs okay in SQL 2000 but when I try to run the same query
in 2005 it hans the server.
any suggestions
thxWhat is the nature of the query?
What are the differences in hardware?
Are you using the same network connection?
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
Make SQL Server faster - www.quicksqlserver.com
___________________________________
"stoney" <stoney@.discussions.microsoft.com> wrote in message
news:FB83E362-FA12-471C-AA36-52F1C0DDD3FA@.microsoft.com...
> Hi,
> Have a query that runs okay in SQL 2000 but when I try to run the same
query
> in 2005 it hans the server.
> any suggestions
> thx|||stoney wrote:
> Hi,
> Have a query that runs okay in SQL 2000 but when I try to run the same que
ry
> in 2005 it hans the server.
> any suggestions
> thx
Begin by comparing the Estimated Execution Plan for the query in 2000
vs. the plan in 2005. Are they different? Assuming the database in
2005 was migrated from 2000, did you update statistics after the migration?
When the query hangs, check the master..sysprocesses table, does
anything show as being blocked?
Tracy McKibben
MCDBA
http://www.realsqlguy.comsql

query hangs

Hi,
Have a query that runs okay in SQL 2000 but when I try to run the same query
in 2005 it hans the server.
any suggestions
thxWhat is the nature of the query?
What are the differences in hardware?
Are you using the same network connection?
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
Make SQL Server faster - www.quicksqlserver.com
___________________________________
"stoney" <stoney@.discussions.microsoft.com> wrote in message
news:FB83E362-FA12-471C-AA36-52F1C0DDD3FA@.microsoft.com...
> Hi,
> Have a query that runs okay in SQL 2000 but when I try to run the same
query
> in 2005 it hans the server.
> any suggestions
> thx|||stoney wrote:
> Hi,
> Have a query that runs okay in SQL 2000 but when I try to run the same query
> in 2005 it hans the server.
> any suggestions
> thx
Begin by comparing the Estimated Execution Plan for the query in 2000
vs. the plan in 2005. Are they different? Assuming the database in
2005 was migrated from 2000, did you update statistics after the migration?
When the query hangs, check the master..sysprocesses table, does
anything show as being blocked?
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Monday, March 12, 2012

Query doesn't hangs when run using parameters

I have a weird problem in Sql Server 2005 that I need some help on. Below
you'll find two queries. Both queries are exactly the same; they query the
same database, query for the same info, same sql statement, same where
clause... The first query (the one with all the parameters included in the
sql statement) runs perfectly against my database in less than a second. The
second query is as I've said exactly the same, the only difference is that
the parameters are now passed using the SqlCommand.Parameters-collection. The
problem with this query is that it seems to cause Sql Server to hang. It
seems to execute my query (CPU varies between 70% and 100%) but it never
finishes. Last time I cancelled it after +20 minutes. Does anybody why I see
this behavior? Is there something that I can do about it? For what it's
worth, my database hardly contains any data. Since it never seems to finish,
I'm not able to generate a execute plan for it to see what the problem is.
The good query:
SELECT a.[ID], a.[CreatedOn], a.[CreatedBy], a.[ModifiedOn], a.[ModifiedBy],
a.[Tag], a.[FileCount]
FROM tblRECORDS a
WHERE a.[ID] IN (
SELECT a.[ID]
FROM tblRECORDS a INNER JOIN getRECORDFULLTEXT b
ON a.[Id] = b.[RecordId] INNER JOIN getRECORDFULLTEXT c
ON a.[Id] = c.[RecordId] INNER JOIN getRECORDFULLTEXT d
ON a.[Id] = d.[RecordId] INNER JOIN getRECORDFULLTEXT e
ON a.[Id] = e.[RecordId] INNER JOIN getRECORDFULLTEXT f
ON a.[Id] = f.[RecordId]
WHERE
(
(
(
(
b.[LanguageId] = 'c2bd4f9b-bb95-4bcb-80c3-1e924c9c26dc'
AND
b.[Keywords] LIKE 'Dit %' ESCAPE '~'
)
AND
(
c.[LanguageId] = 'c2bd4f9b-bb95-4bcb-80c3-1e924c9c26dc'
AND
c.[Keywords] LIKE 'de %' ESCAPE '~'
)
AND
(
d.[LanguageId] = 'c2bd4f9b-bb95-4bcb-80c3-1e924c9c26dc'
AND
d.[Keywords] LIKE 'nederlandse %' ESCAPE '~'
)
AND
(
e.[LanguageId] = 'c2bd4f9b-bb95-4bcb-80c3-1e924c9c26dc'
AND
e.[Keywords] LIKE 'eerste %' ESCAPE '~'
)
AND
(
f.[LanguageId] = 'c2bd4f9b-bb95-4bcb-80c3-1e924c9c26dc'
AND
f.[Keywords] LIKE 'optie %' ESCAPE '~'
)
)
OR
(
(
b.[LanguageId] = '00000000-0000-0000-0000-000000000000'
AND
b.[Keywords] LIKE 'Dit %' ESCAPE '~'
)
AND
(
c.[LanguageId] = '00000000-0000-0000-0000-000000000000'
AND
c.[Keywords] LIKE 'de %' ESCAPE '~'
)
AND
(
d.[LanguageId] = '00000000-0000-0000-0000-000000000000'
AND
d.[Keywords] LIKE 'nederlandse %' ESCAPE '~'
)
AND
(
e.[LanguageId] = '00000000-0000-0000-0000-000000000000'
AND
e.[Keywords] LIKE 'eerste %' ESCAPE '~'
)
AND
(
f.[LanguageId] = '00000000-0000-0000-0000-000000000000'
AND
f.[Keywords] LIKE 'optie %' ESCAPE '~'
)
)
) AND a.[ID] = 'a6ff3da9-6618-4f62-9a24-79aad5e755ca'
)
)
The bad query:
exec sp_executesql N'
SELECT a.[ID], a.[CreatedOn], a.[CreatedBy], a.[ModifiedOn],
a.[ModifiedBy], a.[Tag], a.[FileCount]
FROM tblRECORDS a
WHERE a.[ID] IN
(
SELECT a.[ID] FROM tblRECORDS a INNER JOIN getRECORDFULLTEXT b
ON a.[Id] = b.[RecordId] INNER JOIN getRECORDFULLTEXT c
ON a.[Id] = c.[RecordId] INNER JOIN getRECORDFULLTEXT d
ON a.[Id] = d.[RecordId] INNER JOIN getRECORDFULLTEXT e
ON a.[Id] = e.[RecordId] INNER JOIN getRECORDFULLTEXT f
ON a.[Id] = f.[RecordId]
WHERE
(
(
(
(
b.[LanguageId] = @.__0
AND
b.[Keywords] LIKE @.__1 ESCAPE ''~''
)
AND
(
c.[LanguageId] = @.__2
AND
c.[Keywords] LIKE @.__3 ESCAPE ''~''
)
AND
(
d.[LanguageId] = @.__4
AND
d.[Keywords] LIKE @.__5 ESCAPE ''~''
)
AND
(
e.[LanguageId] = @.__6
AND
e.[Keywords] LIKE @.__7 ESCAPE ''~''
)
AND
(
f.[LanguageId] = @.__8
AND
f.[Keywords] LIKE @.__9 ESCAPE ''~''
)
)
OR
(
(
b.[LanguageId] = @.__10
AND
b.[Keywords] LIKE @.__11 ESCAPE ''~''
)
AND
(
c.[LanguageId] = @.__12
AND
c.[Keywords] LIKE @.__13 ESCAPE ''~''
)
AND
(
d.[LanguageId] = @.__14
AND
d.[Keywords] LIKE @.__15 ESCAPE ''~''
)
AND
(
e.[LanguageId] = @.__16
AND
e.[Keywords] LIKE @.__17 ESCAPE ''~''
)
AND
(
f.[LanguageId] = @.__18
AND
f.[Keywords] LIKE @.__19 ESCAPE ''~''
)
)
)
AND
a.[ID] = @.__20
)
)',
N'@.__0 uniqueidentifier,@.__1 nvarchar(5),@.__2 uniqueidentifier,@.__3
nvarchar(4),
@.__4 uniqueidentifier,@.__5 nvarchar(13),@.__6 uniqueidentifier,@.__7
nvarchar(8),
@.__8 uniqueidentifier,@.__9 nvarchar(7),@.__10 uniqueidentifier,@.__11
nvarchar(5),
@.__12 uniqueidentifier,@.__13 nvarchar(4),@.__14 uniqueidentifier,@.__15
nvarchar(13),
@.__16 uniqueidentifier,@.__17 nvarchar(8),@.__18 uniqueidentifier,@.__19
nvarchar(7),
@.__20
uniqueidentifier',@.__0='C2BD4F9B-BB95-4BCB-80C3-1E924C9C26DC',@.__1=N'Dit %',
@.__2='C2BD4F9B-BB95-4BCB-80C3-1E924C9C26DC',@.__3=N'de %',
@.__4='C2BD4F9B-BB95-4BCB-80C3-1E924C9C26DC',@.__5=N'nederlandse %',
@.__6='C2BD4F9B-BB95-4BCB-80C3-1E924C9C26DC',@.__7=N'eerste %',
@.__8='C2BD4F9B-BB95-4BCB-80C3-1E924C9C26DC',@.__9=N'optie %',
@.__10='00000000-0000-0000-0000-000000000000',@.__11=N'Dit %',
@.__12='00000000-0000-0000-0000-000000000000',@.__13=N'de %',
@.__14='00000000-0000-0000-0000-000000000000',@.__15=N'nederlandse %',
@.__16='00000000-0000-0000-0000-000000000000',@.__17=N'eerste %',
@.__18='00000000-0000-0000-0000-000000000000',@.__19=N'optie %',
@.__20='a6ff3da9-6618-4f62-9a24-79aad5e755ca'Michael Vanhoutte wrote:
> I have a weird problem in Sql Server 2005 that I need some help on. Below
> you'll find two queries. Both queries are exactly the same; they query the
> same database, query for the same info, same sql statement, same where
> clause... The first query (the one with all the parameters included in the
> sql statement) runs perfectly against my database in less than a second. The
> second query is as I've said exactly the same, the only difference is that
> the parameters are now passed using the SqlCommand.Parameters-collection. The
> problem with this query is that it seems to cause Sql Server to hang. It
> seems to execute my query (CPU varies between 70% and 100%) but it never
> finishes. Last time I cancelled it after +20 minutes. Does anybody why I see
> this behavior? Is there something that I can do about it? For what it's
> worth, my database hardly contains any data. Since it never seems to finish,
> I'm not able to generate a execute plan for it to see what the problem is.
> The good query:
> SELECT a.[ID], a.[CreatedOn], a.[CreatedBy], a.[ModifiedOn], a.[ModifiedBy],
> a.[Tag], a.[FileCount]
> FROM tblRECORDS a
> WHERE a.[ID] IN (
> SELECT a.[ID]
> FROM tblRECORDS a INNER JOIN getRECORDFULLTEXT b
> ON a.[Id] = b.[RecordId] INNER JOIN getRECORDFULLTEXT c
> ON a.[Id] = c.[RecordId] INNER JOIN getRECORDFULLTEXT d
> ON a.[Id] = d.[RecordId] INNER JOIN getRECORDFULLTEXT e
> ON a.[Id] = e.[RecordId] INNER JOIN getRECORDFULLTEXT f
> ON a.[Id] = f.[RecordId]
> WHERE
> (
> (
> (
> (
> b.[LanguageId] = 'c2bd4f9b-bb95-4bcb-80c3-1e924c9c26dc'
> AND
> b.[Keywords] LIKE 'Dit %' ESCAPE '~'
> )
> AND
> (
> c.[LanguageId] = 'c2bd4f9b-bb95-4bcb-80c3-1e924c9c26dc'
> AND
> c.[Keywords] LIKE 'de %' ESCAPE '~'
> )
> AND
> (
> d.[LanguageId] = 'c2bd4f9b-bb95-4bcb-80c3-1e924c9c26dc'
> AND
> d.[Keywords] LIKE 'nederlandse %' ESCAPE '~'
> )
> AND
> (
> e.[LanguageId] = 'c2bd4f9b-bb95-4bcb-80c3-1e924c9c26dc'
> AND
> e.[Keywords] LIKE 'eerste %' ESCAPE '~'
> )
> AND
> (
> f.[LanguageId] = 'c2bd4f9b-bb95-4bcb-80c3-1e924c9c26dc'
> AND
> f.[Keywords] LIKE 'optie %' ESCAPE '~'
> )
> )
> OR
> (
> (
> b.[LanguageId] = '00000000-0000-0000-0000-000000000000'
> AND
> b.[Keywords] LIKE 'Dit %' ESCAPE '~'
> )
> AND
> (
> c.[LanguageId] = '00000000-0000-0000-0000-000000000000'
> AND
> c.[Keywords] LIKE 'de %' ESCAPE '~'
> )
> AND
> (
> d.[LanguageId] = '00000000-0000-0000-0000-000000000000'
> AND
> d.[Keywords] LIKE 'nederlandse %' ESCAPE '~'
> )
> AND
> (
> e.[LanguageId] = '00000000-0000-0000-0000-000000000000'
> AND
> e.[Keywords] LIKE 'eerste %' ESCAPE '~'
> )
> AND
> (
> f.[LanguageId] = '00000000-0000-0000-0000-000000000000'
> AND
> f.[Keywords] LIKE 'optie %' ESCAPE '~'
> )
> )
> ) AND a.[ID] = 'a6ff3da9-6618-4f62-9a24-79aad5e755ca'
> )
> )
> The bad query:
> exec sp_executesql N'
> SELECT a.[ID], a.[CreatedOn], a.[CreatedBy], a.[ModifiedOn],
> a.[ModifiedBy], a.[Tag], a.[FileCount]
> FROM tblRECORDS a
> WHERE a.[ID] IN
> (
> SELECT a.[ID] FROM tblRECORDS a INNER JOIN getRECORDFULLTEXT b
> ON a.[Id] = b.[RecordId] INNER JOIN getRECORDFULLTEXT c
> ON a.[Id] = c.[RecordId] INNER JOIN getRECORDFULLTEXT d
> ON a.[Id] = d.[RecordId] INNER JOIN getRECORDFULLTEXT e
> ON a.[Id] = e.[RecordId] INNER JOIN getRECORDFULLTEXT f
> ON a.[Id] = f.[RecordId]
> WHERE
> (
> (
> (
> (
> b.[LanguageId] = @.__0
> AND
> b.[Keywords] LIKE @.__1 ESCAPE ''~''
> )
> AND
> (
> c.[LanguageId] = @.__2
> AND
> c.[Keywords] LIKE @.__3 ESCAPE ''~''
> )
> AND
> (
> d.[LanguageId] = @.__4
> AND
> d.[Keywords] LIKE @.__5 ESCAPE ''~''
> )
> AND
> (
> e.[LanguageId] = @.__6
> AND
> e.[Keywords] LIKE @.__7 ESCAPE ''~''
> )
> AND
> (
> f.[LanguageId] = @.__8
> AND
> f.[Keywords] LIKE @.__9 ESCAPE ''~''
> )
> )
> OR
> (
> (
> b.[LanguageId] = @.__10
> AND
> b.[Keywords] LIKE @.__11 ESCAPE ''~''
> )
> AND
> (
> c.[LanguageId] = @.__12
> AND
> c.[Keywords] LIKE @.__13 ESCAPE ''~''
> )
> AND
> (
> d.[LanguageId] = @.__14
> AND
> d.[Keywords] LIKE @.__15 ESCAPE ''~''
> )
> AND
> (
> e.[LanguageId] = @.__16
> AND
> e.[Keywords] LIKE @.__17 ESCAPE ''~''
> )
> AND
> (
> f.[LanguageId] = @.__18
> AND
> f.[Keywords] LIKE @.__19 ESCAPE ''~''
> )
> )
> )
> AND
> a.[ID] = @.__20
> )
> )',
> N'@.__0 uniqueidentifier,@.__1 nvarchar(5),@.__2 uniqueidentifier,@.__3
> nvarchar(4),
> @.__4 uniqueidentifier,@.__5 nvarchar(13),@.__6 uniqueidentifier,@.__7
> nvarchar(8),
> @.__8 uniqueidentifier,@.__9 nvarchar(7),@.__10 uniqueidentifier,@.__11
> nvarchar(5),
> @.__12 uniqueidentifier,@.__13 nvarchar(4),@.__14 uniqueidentifier,@.__15
> nvarchar(13),
> @.__16 uniqueidentifier,@.__17 nvarchar(8),@.__18 uniqueidentifier,@.__19
> nvarchar(7),
> @.__20
> uniqueidentifier',@.__0='C2BD4F9B-BB95-4BCB-80C3-1E924C9C26DC',@.__1=N'Dit %',
> @.__2='C2BD4F9B-BB95-4BCB-80C3-1E924C9C26DC',@.__3=N'de %',
> @.__4='C2BD4F9B-BB95-4BCB-80C3-1E924C9C26DC',@.__5=N'nederlandse %',
> @.__6='C2BD4F9B-BB95-4BCB-80C3-1E924C9C26DC',@.__7=N'eerste %',
> @.__8='C2BD4F9B-BB95-4BCB-80C3-1E924C9C26DC',@.__9=N'optie %',
> @.__10='00000000-0000-0000-0000-000000000000',@.__11=N'Dit %',
> @.__12='00000000-0000-0000-0000-000000000000',@.__13=N'de %',
> @.__14='00000000-0000-0000-0000-000000000000',@.__15=N'nederlandse %',
> @.__16='00000000-0000-0000-0000-000000000000',@.__17=N'eerste %',
> @.__18='00000000-0000-0000-0000-000000000000',@.__19=N'optie %',
> @.__20='a6ff3da9-6618-4f62-9a24-79aad5e755ca'
Do a Google search for "parameter sniffing"
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi Tracy,
Thanks for your reply. First of all, sorry for the brilliant typo in my
subject. I only noticed that now. ;-)
I see where you're going with your answer. I know that Sql Server caches the
execution plan of a query and that you might sometimes get a slower response
if it happens to use an execution plan that is not optimal for your
parameters.
However, I wonder if there's not something more going on here. The reason
why I'm saying this is that my table hardly contains any data. The biggest
table involved in my query contains a few dozen records. I mean that
literally: 61 records to be exact. There are a few other tables involved in
the view but they contain even fewer records. Most less than 10. So, even if
the execution plan would be completely wrong and Sql Server would use table
scans for everything, it should still finish instantaneously. Could the
impact really be this bad that it takes +20 minutes to query and join a few
tables that all contain less than 100 rows?
By the way, I have tried using RECOMPILE and the query does seem to run at
normal speed now.
Regards,
Michael
"Tracy McKibben" wrote:
> Michael Vanhoutte wrote:
> > I have a weird problem in Sql Server 2005 that I need some help on. Below
> > you'll find two queries. Both queries are exactly the same; they query the
> > same database, query for the same info, same sql statement, same where
> > clause... The first query (the one with all the parameters included in the
> > sql statement) runs perfectly against my database in less than a second. The
> > second query is as I've said exactly the same, the only difference is that
> > the parameters are now passed using the SqlCommand.Parameters-collection. The
> > problem with this query is that it seems to cause Sql Server to hang. It
> > seems to execute my query (CPU varies between 70% and 100%) but it never
> > finishes. Last time I cancelled it after +20 minutes. Does anybody why I see
> > this behavior? Is there something that I can do about it? For what it's
> > worth, my database hardly contains any data. Since it never seems to finish,
> > I'm not able to generate a execute plan for it to see what the problem is.
> >
> > The good query:
> > SELECT a.[ID], a.[CreatedOn], a.[CreatedBy], a.[ModifiedOn], a.[ModifiedBy],
> > a.[Tag], a.[FileCount]
> > FROM tblRECORDS a
> > WHERE a.[ID] IN (
> > SELECT a.[ID]
> > FROM tblRECORDS a INNER JOIN getRECORDFULLTEXT b
> > ON a.[Id] = b.[RecordId] INNER JOIN getRECORDFULLTEXT c
> > ON a.[Id] = c.[RecordId] INNER JOIN getRECORDFULLTEXT d
> > ON a.[Id] = d.[RecordId] INNER JOIN getRECORDFULLTEXT e
> > ON a.[Id] = e.[RecordId] INNER JOIN getRECORDFULLTEXT f
> > ON a.[Id] = f.[RecordId]
> > WHERE
> > (
> > (
> > (
> > (
> > b.[LanguageId] = 'c2bd4f9b-bb95-4bcb-80c3-1e924c9c26dc'
> > AND
> > b.[Keywords] LIKE 'Dit %' ESCAPE '~'
> > )
> > AND
> > (
> > c.[LanguageId] = 'c2bd4f9b-bb95-4bcb-80c3-1e924c9c26dc'
> > AND
> > c.[Keywords] LIKE 'de %' ESCAPE '~'
> > )
> > AND
> > (
> > d.[LanguageId] = 'c2bd4f9b-bb95-4bcb-80c3-1e924c9c26dc'
> > AND
> > d.[Keywords] LIKE 'nederlandse %' ESCAPE '~'
> > )
> > AND
> > (
> > e.[LanguageId] = 'c2bd4f9b-bb95-4bcb-80c3-1e924c9c26dc'
> > AND
> > e.[Keywords] LIKE 'eerste %' ESCAPE '~'
> > )
> > AND
> > (
> > f.[LanguageId] = 'c2bd4f9b-bb95-4bcb-80c3-1e924c9c26dc'
> > AND
> > f.[Keywords] LIKE 'optie %' ESCAPE '~'
> > )
> > )
> > OR
> > (
> > (
> > b.[LanguageId] = '00000000-0000-0000-0000-000000000000'
> > AND
> > b.[Keywords] LIKE 'Dit %' ESCAPE '~'
> > )
> > AND
> > (
> > c.[LanguageId] = '00000000-0000-0000-0000-000000000000'
> > AND
> > c.[Keywords] LIKE 'de %' ESCAPE '~'
> > )
> > AND
> > (
> > d.[LanguageId] = '00000000-0000-0000-0000-000000000000'
> > AND
> > d.[Keywords] LIKE 'nederlandse %' ESCAPE '~'
> > )
> > AND
> > (
> > e.[LanguageId] = '00000000-0000-0000-0000-000000000000'
> > AND
> > e.[Keywords] LIKE 'eerste %' ESCAPE '~'
> > )
> > AND
> > (
> > f.[LanguageId] = '00000000-0000-0000-0000-000000000000'
> > AND
> > f.[Keywords] LIKE 'optie %' ESCAPE '~'
> > )
> > )
> > ) AND a.[ID] = 'a6ff3da9-6618-4f62-9a24-79aad5e755ca'
> > )
> > )
> >
> > The bad query:
> > exec sp_executesql N'
> > SELECT a.[ID], a.[CreatedOn], a.[CreatedBy], a.[ModifiedOn],
> > a.[ModifiedBy], a.[Tag], a.[FileCount]
> > FROM tblRECORDS a
> > WHERE a.[ID] IN
> > (
> > SELECT a.[ID] FROM tblRECORDS a INNER JOIN getRECORDFULLTEXT b
> > ON a.[Id] = b.[RecordId] INNER JOIN getRECORDFULLTEXT c
> > ON a.[Id] = c.[RecordId] INNER JOIN getRECORDFULLTEXT d
> > ON a.[Id] = d.[RecordId] INNER JOIN getRECORDFULLTEXT e
> > ON a.[Id] = e.[RecordId] INNER JOIN getRECORDFULLTEXT f
> > ON a.[Id] = f.[RecordId]
> > WHERE
> > (
> > (
> > (
> > (
> > b.[LanguageId] = @.__0
> > AND
> > b.[Keywords] LIKE @.__1 ESCAPE ''~''
> > )
> > AND
> > (
> > c.[LanguageId] = @.__2
> > AND
> > c.[Keywords] LIKE @.__3 ESCAPE ''~''
> > )
> > AND
> > (
> > d.[LanguageId] = @.__4
> > AND
> > d.[Keywords] LIKE @.__5 ESCAPE ''~''
> > )
> > AND
> > (
> > e.[LanguageId] = @.__6
> > AND
> > e.[Keywords] LIKE @.__7 ESCAPE ''~''
> > )
> > AND
> > (
> > f.[LanguageId] = @.__8
> > AND
> > f.[Keywords] LIKE @.__9 ESCAPE ''~''
> > )
> > )
> > OR
> > (
> > (
> > b.[LanguageId] = @.__10
> > AND
> > b.[Keywords] LIKE @.__11 ESCAPE ''~''
> > )
> > AND
> > (
> > c.[LanguageId] = @.__12
> > AND
> > c.[Keywords] LIKE @.__13 ESCAPE ''~''
> > )
> > AND
> > (
> > d.[LanguageId] = @.__14
> > AND
> > d.[Keywords] LIKE @.__15 ESCAPE ''~''
> > )
> > AND
> > (
> > e.[LanguageId] = @.__16
> > AND
> > e.[Keywords] LIKE @.__17 ESCAPE ''~''
> > )
> > AND
> > (
> > f.[LanguageId] = @.__18
> > AND
> > f.[Keywords] LIKE @.__19 ESCAPE ''~''
> > )
> > )
> > )
> > AND
> > a.[ID] = @.__20
> > )
> > )',
> > N'@.__0 uniqueidentifier,@.__1 nvarchar(5),@.__2 uniqueidentifier,@.__3
> > nvarchar(4),
> > @.__4 uniqueidentifier,@.__5 nvarchar(13),@.__6 uniqueidentifier,@.__7
> > nvarchar(8),
> > @.__8 uniqueidentifier,@.__9 nvarchar(7),@.__10 uniqueidentifier,@.__11
> > nvarchar(5),
> > @.__12 uniqueidentifier,@.__13 nvarchar(4),@.__14 uniqueidentifier,@.__15
> > nvarchar(13),
> > @.__16 uniqueidentifier,@.__17 nvarchar(8),@.__18 uniqueidentifier,@.__19
> > nvarchar(7),
> > @.__20
> > uniqueidentifier',@.__0='C2BD4F9B-BB95-4BCB-80C3-1E924C9C26DC',@.__1=N'Dit %',
> > @.__2='C2BD4F9B-BB95-4BCB-80C3-1E924C9C26DC',@.__3=N'de %',
> > @.__4='C2BD4F9B-BB95-4BCB-80C3-1E924C9C26DC',@.__5=N'nederlandse %',
> > @.__6='C2BD4F9B-BB95-4BCB-80C3-1E924C9C26DC',@.__7=N'eerste %',
> > @.__8='C2BD4F9B-BB95-4BCB-80C3-1E924C9C26DC',@.__9=N'optie %',
> > @.__10='00000000-0000-0000-0000-000000000000',@.__11=N'Dit %',
> > @.__12='00000000-0000-0000-0000-000000000000',@.__13=N'de %',
> > @.__14='00000000-0000-0000-0000-000000000000',@.__15=N'nederlandse %',
> > @.__16='00000000-0000-0000-0000-000000000000',@.__17=N'eerste %',
> > @.__18='00000000-0000-0000-0000-000000000000',@.__19=N'optie %',
> > @.__20='a6ff3da9-6618-4f62-9a24-79aad5e755ca'
> Do a Google search for "parameter sniffing"
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||Michael Vanhoutte wrote:
> Hi Tracy,
> Thanks for your reply. First of all, sorry for the brilliant typo in my
> subject. I only noticed that now. ;-)
> I see where you're going with your answer. I know that Sql Server caches the
> execution plan of a query and that you might sometimes get a slower response
> if it happens to use an execution plan that is not optimal for your
> parameters.
> However, I wonder if there's not something more going on here. The reason
> why I'm saying this is that my table hardly contains any data. The biggest
> table involved in my query contains a few dozen records. I mean that
> literally: 61 records to be exact. There are a few other tables involved in
> the view but they contain even fewer records. Most less than 10. So, even if
> the execution plan would be completely wrong and Sql Server would use table
> scans for everything, it should still finish instantaneously. Could the
> impact really be this bad that it takes +20 minutes to query and join a few
> tables that all contain less than 100 rows?
> By the way, I have tried using RECOMPILE and the query does seem to run at
> normal speed now.
20 minutes does seem excessive for such little data, but it's still not
impossible to believe. Worst case, as you said, is that the execution
plan is so bad that it falls down to doing full table scans on all the
involved tables. That makes you vulnerable to a couple of things:
1. Blocking - you don't say how busy these tables are, so this is
speculation. Assuming that other processes are using these tables, your
table scans could be blocked. Even SELECTs can place locks on a table,
and those locks will interfere with a scan.
2. Resource contention - without knowing your hardware specs, this is
also speculation. If the server is extremely tight on memory, these
tables might have to be read from disk when the scan occurs. If your
drives are I/O bound, these reads will be affected.
There could be something other than parameter sniffing at work here, but
based on the symptoms, I'm going to stick with that as my answer.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Today, I wanted to update my code to include the RECOMPILE-option in the
query but the first time I executed the query with the RECOMPILE-option on, I
noticed that it again took forever to run. I have let it run for over two and
a half hours now and it is still busy, so I'm guessing that something else is
going on.
Some more info:
1. The query runs against the Sql Server on my laptop. I have plenty of
memory available when the query starts and my Sql Server is not used at all
except to run this query. No other users are connected.
2. Before the query starts, Sql Server is using about 150MB of RAM (of the
1,5GB that I have on my laptop) and after a couple of minutes, the memory
used by Sql Server increases up to more than 1GB. So, memory usage to run
this particular query is enormous. This is weird because as I've said it
queries tables that almost don't contain any records and it should return a
single record in the end.
3. I have checked in Activity Monitor while the query is running and there
are no blocking or blocked processes.
4. Also, the Sql Server process is constantly using CPU, with an average of
about 25% during the last two hours.
5. I have constant disc activity while the query is running
6. My laptop is simply not usable anymore while the query is running. Sql
Server is using so much memory and disc activity that anything I try to do
takes ages.
Any other insights on what might be causing this problem?
"Tracy McKibben" wrote:
> Michael Vanhoutte wrote:
> > Hi Tracy,
> >
> > Thanks for your reply. First of all, sorry for the brilliant typo in my
> > subject. I only noticed that now. ;-)
> >
> > I see where you're going with your answer. I know that Sql Server caches the
> > execution plan of a query and that you might sometimes get a slower response
> > if it happens to use an execution plan that is not optimal for your
> > parameters.
> >
> > However, I wonder if there's not something more going on here. The reason
> > why I'm saying this is that my table hardly contains any data. The biggest
> > table involved in my query contains a few dozen records. I mean that
> > literally: 61 records to be exact. There are a few other tables involved in
> > the view but they contain even fewer records. Most less than 10. So, even if
> > the execution plan would be completely wrong and Sql Server would use table
> > scans for everything, it should still finish instantaneously. Could the
> > impact really be this bad that it takes +20 minutes to query and join a few
> > tables that all contain less than 100 rows?
> >
> > By the way, I have tried using RECOMPILE and the query does seem to run at
> > normal speed now.
> 20 minutes does seem excessive for such little data, but it's still not
> impossible to believe. Worst case, as you said, is that the execution
> plan is so bad that it falls down to doing full table scans on all the
> involved tables. That makes you vulnerable to a couple of things:
> 1. Blocking - you don't say how busy these tables are, so this is
> speculation. Assuming that other processes are using these tables, your
> table scans could be blocked. Even SELECTs can place locks on a table,
> and those locks will interfere with a scan.
> 2. Resource contention - without knowing your hardware specs, this is
> also speculation. If the server is extremely tight on memory, these
> tables might have to be read from disk when the scan occurs. If your
> drives are I/O bound, these reads will be affected.
> There could be something other than parameter sniffing at work here, but
> based on the symptoms, I'm going to stick with that as my answer.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||Michael Vanhoutte wrote:
> Today, I wanted to update my code to include the RECOMPILE-option in the
> query but the first time I executed the query with the RECOMPILE-option on, I
> noticed that it again took forever to run. I have let it run for over two and
> a half hours now and it is still busy, so I'm guessing that something else is
> going on.
> Some more info:
> 1. The query runs against the Sql Server on my laptop. I have plenty of
> memory available when the query starts and my Sql Server is not used at all
> except to run this query. No other users are connected.
> 2. Before the query starts, Sql Server is using about 150MB of RAM (of the
> 1,5GB that I have on my laptop) and after a couple of minutes, the memory
> used by Sql Server increases up to more than 1GB. So, memory usage to run
> this particular query is enormous. This is weird because as I've said it
> queries tables that almost don't contain any records and it should return a
> single record in the end.
> 3. I have checked in Activity Monitor while the query is running and there
> are no blocking or blocked processes.
> 4. Also, the Sql Server process is constantly using CPU, with an average of
> about 25% during the last two hours.
> 5. I have constant disc activity while the query is running
> 6. My laptop is simply not usable anymore while the query is running. Sql
> Server is using so much memory and disc activity that anything I try to do
> takes ages.
> Any other insights on what might be causing this problem?
>
You should probably try to get a Profiler trace to see what SQL is doing
while the machine is getting hammered...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I don't believe it... I wanted to run a more detailed profiler trace today to
get some more information. I had already run a default trace earlier, but all
that got me was an RPC Starting-event with the Sql statement that was in my
report. Today, I activated ALL events in profiler and I started the query.
Guess what? I can't reproduce the problem anymore! Nothing has changed as far
as data, structure, queries... I'm guessing that Sql Server automatically
updated some statistics which causes it to use a different execution plan.
This morning, I said to myself to take a backup of the database while the
problem is still there, but apparently I'm too late. We'll see in the coming
days if the problem comes back. Thanks for your help so far!
"Tracy McKibben" wrote:
> Michael Vanhoutte wrote:
> > Today, I wanted to update my code to include the RECOMPILE-option in the
> > query but the first time I executed the query with the RECOMPILE-option on, I
> > noticed that it again took forever to run. I have let it run for over two and
> > a half hours now and it is still busy, so I'm guessing that something else is
> > going on.
> >
> > Some more info:
> > 1. The query runs against the Sql Server on my laptop. I have plenty of
> > memory available when the query starts and my Sql Server is not used at all
> > except to run this query. No other users are connected.
> > 2. Before the query starts, Sql Server is using about 150MB of RAM (of the
> > 1,5GB that I have on my laptop) and after a couple of minutes, the memory
> > used by Sql Server increases up to more than 1GB. So, memory usage to run
> > this particular query is enormous. This is weird because as I've said it
> > queries tables that almost don't contain any records and it should return a
> > single record in the end.
> > 3. I have checked in Activity Monitor while the query is running and there
> > are no blocking or blocked processes.
> > 4. Also, the Sql Server process is constantly using CPU, with an average of
> > about 25% during the last two hours.
> > 5. I have constant disc activity while the query is running
> > 6. My laptop is simply not usable anymore while the query is running. Sql
> > Server is using so much memory and disc activity that anything I try to do
> > takes ages.
> >
> > Any other insights on what might be causing this problem?
> >
> You should probably try to get a Profiler trace to see what SQL is doing
> while the machine is getting hammered...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||Michael Vanhoutte wrote:
> I don't believe it... I wanted to run a more detailed profiler trace today to
> get some more information. I had already run a default trace earlier, but all
> that got me was an RPC Starting-event with the Sql statement that was in my
> report. Today, I activated ALL events in profiler and I started the query.
> Guess what? I can't reproduce the problem anymore! Nothing has changed as far
> as data, structure, queries... I'm guessing that Sql Server automatically
> updated some statistics which causes it to use a different execution plan.
> This morning, I said to myself to take a backup of the database while the
> problem is still there, but apparently I'm too late. We'll see in the coming
> days if the problem comes back. Thanks for your help so far!
>
If you have auto-stats enabled, or run a scheduled stats update, that
very well could be what happened. Or, it could have been an inefficient
query plan that was cached, and has since dropped out of the cache.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Wednesday, March 28, 2012

Query Hangs/Time out

Query Hangs/Time out

Query Hangs/Time out

Monday, March 26, 2012

Query Hangs when Parameters are scaled down.

Query Hangs when Parameters are scaled down.

Query Hangs when Parameters are scaled down.

Query Hangs on Values that start with a number

Query Hangs on Values that start with a number

Query Hangs on Values that start with a number

Query hangs during a seperate write

query hangs

query hangs

Monday, March 12, 2012

Query doesn't hangs when run using parameters

query assistance

Blog Archive

About Me