We have recently upgraded 4 SQL servers to Windows 2003, running SQL Server
2000. Many people are complaining that Enterprise Manager now takes 4-20
seconds to expand the databases on those servers, where before it was either
instant or 1-2 seconds. Once the intial connection has been made, there are
no further performance issues. Do you know if there is a fix, or have others
experienced this slow performance after the upgrade?
We have compared user desktops' memory, processors, hard drives, etc, and
there is no trend there.
We would appreciate any advice before moving forward w/ our other servers'
upgrades!To answer my own question, in case others have experienced this, there was a
database with the "AutoClose" option checked. This was causing the
performance hit in Enterprise Manager after we upgraded to 2003. For
whatever reason, this did not cause problems on a 2000 OS, but definitely
caused issues on 2003 OS.
Resolution, just went into properties for the database and unchecked it.
This increased performance back to its original state. We now have a script
that checks and ensures database properties are set correctly.
Hope this saves someone else out there the headaches we went through trying
to determine the performance issues after upgrading to Windows 2003.
"JaneDoe" wrote:
> We have recently upgraded 4 SQL servers to Windows 2003, running SQL Server
> 2000. Many people are complaining that Enterprise Manager now takes 4-20
> seconds to expand the databases on those servers, where before it was either
> instant or 1-2 seconds. Once the intial connection has been made, there are
> no further performance issues. Do you know if there is a fix, or have others
> experienced this slow performance after the upgrade?
> We have compared user desktops' memory, processors, hard drives, etc, and
> there is no trend there.
> We would appreciate any advice before moving forward w/ our other servers'
> upgrades!
Showing posts with label hits. Show all posts
Showing posts with label hits. Show all posts
Friday, March 30, 2012
Performance Hits within EM After Upgrading OS to Windows 2003
We have recently upgraded 4 SQL servers to Windows 2003, running SQL Server
2000. Many people are complaining that Enterprise Manager now takes 4-20
seconds to expand the databases on those servers, where before it was either
instant or 1-2 seconds. Once the intial connection has been made, there are
no further performance issues. Do you know if there is a fix, or have other
s
experienced this slow performance after the upgrade?
We have compared user desktops' memory, processors, hard drives, etc, and
there is no trend there.
We would appreciate any advice before moving forward w/ our other servers'
upgrades!To answer my own question, in case others have experienced this, there was a
database with the "AutoClose" option checked. This was causing the
performance hit in Enterprise Manager after we upgraded to 2003. For
whatever reason, this did not cause problems on a 2000 OS, but definitely
caused issues on 2003 OS.
Resolution, just went into properties for the database and unchecked it.
This increased performance back to its original state. We now have a script
that checks and ensures database properties are set correctly.
Hope this saves someone else out there the headaches we went through trying
to determine the performance issues after upgrading to Windows 2003.
"JaneDoe" wrote:
> We have recently upgraded 4 SQL servers to Windows 2003, running SQL Serve
r
> 2000. Many people are complaining that Enterprise Manager now takes 4-20
> seconds to expand the databases on those servers, where before it was eith
er
> instant or 1-2 seconds. Once the intial connection has been made, there a
re
> no further performance issues. Do you know if there is a fix, or have oth
ers
> experienced this slow performance after the upgrade?
> We have compared user desktops' memory, processors, hard drives, etc, and
> there is no trend there.
> We would appreciate any advice before moving forward w/ our other servers'
> upgrades!sql
2000. Many people are complaining that Enterprise Manager now takes 4-20
seconds to expand the databases on those servers, where before it was either
instant or 1-2 seconds. Once the intial connection has been made, there are
no further performance issues. Do you know if there is a fix, or have other
s
experienced this slow performance after the upgrade?
We have compared user desktops' memory, processors, hard drives, etc, and
there is no trend there.
We would appreciate any advice before moving forward w/ our other servers'
upgrades!To answer my own question, in case others have experienced this, there was a
database with the "AutoClose" option checked. This was causing the
performance hit in Enterprise Manager after we upgraded to 2003. For
whatever reason, this did not cause problems on a 2000 OS, but definitely
caused issues on 2003 OS.
Resolution, just went into properties for the database and unchecked it.
This increased performance back to its original state. We now have a script
that checks and ensures database properties are set correctly.
Hope this saves someone else out there the headaches we went through trying
to determine the performance issues after upgrading to Windows 2003.
"JaneDoe" wrote:
> We have recently upgraded 4 SQL servers to Windows 2003, running SQL Serve
r
> 2000. Many people are complaining that Enterprise Manager now takes 4-20
> seconds to expand the databases on those servers, where before it was eith
er
> instant or 1-2 seconds. Once the intial connection has been made, there a
re
> no further performance issues. Do you know if there is a fix, or have oth
ers
> experienced this slow performance after the upgrade?
> We have compared user desktops' memory, processors, hard drives, etc, and
> there is no trend there.
> We would appreciate any advice before moving forward w/ our other servers'
> upgrades!sql
Performance Hits within EM After Upgrading OS to Windows 2003
We have recently upgraded 4 SQL servers to Windows 2003, running SQL Server
2000. Many people are complaining that Enterprise Manager now takes 4-20
seconds to expand the databases on those servers, where before it was either
instant or 1-2 seconds. Once the intial connection has been made, there are
no further performance issues. Do you know if there is a fix, or have others
experienced this slow performance after the upgrade?
We have compared user desktops' memory, processors, hard drives, etc, and
there is no trend there.
We would appreciate any advice before moving forward w/ our other servers'
upgrades!
To answer my own question, in case others have experienced this, there was a
database with the "AutoClose" option checked. This was causing the
performance hit in Enterprise Manager after we upgraded to 2003. For
whatever reason, this did not cause problems on a 2000 OS, but definitely
caused issues on 2003 OS.
Resolution, just went into properties for the database and unchecked it.
This increased performance back to its original state. We now have a script
that checks and ensures database properties are set correctly.
Hope this saves someone else out there the headaches we went through trying
to determine the performance issues after upgrading to Windows 2003.
"JaneDoe" wrote:
> We have recently upgraded 4 SQL servers to Windows 2003, running SQL Server
> 2000. Many people are complaining that Enterprise Manager now takes 4-20
> seconds to expand the databases on those servers, where before it was either
> instant or 1-2 seconds. Once the intial connection has been made, there are
> no further performance issues. Do you know if there is a fix, or have others
> experienced this slow performance after the upgrade?
> We have compared user desktops' memory, processors, hard drives, etc, and
> there is no trend there.
> We would appreciate any advice before moving forward w/ our other servers'
> upgrades!
2000. Many people are complaining that Enterprise Manager now takes 4-20
seconds to expand the databases on those servers, where before it was either
instant or 1-2 seconds. Once the intial connection has been made, there are
no further performance issues. Do you know if there is a fix, or have others
experienced this slow performance after the upgrade?
We have compared user desktops' memory, processors, hard drives, etc, and
there is no trend there.
We would appreciate any advice before moving forward w/ our other servers'
upgrades!
To answer my own question, in case others have experienced this, there was a
database with the "AutoClose" option checked. This was causing the
performance hit in Enterprise Manager after we upgraded to 2003. For
whatever reason, this did not cause problems on a 2000 OS, but definitely
caused issues on 2003 OS.
Resolution, just went into properties for the database and unchecked it.
This increased performance back to its original state. We now have a script
that checks and ensures database properties are set correctly.
Hope this saves someone else out there the headaches we went through trying
to determine the performance issues after upgrading to Windows 2003.
"JaneDoe" wrote:
> We have recently upgraded 4 SQL servers to Windows 2003, running SQL Server
> 2000. Many people are complaining that Enterprise Manager now takes 4-20
> seconds to expand the databases on those servers, where before it was either
> instant or 1-2 seconds. Once the intial connection has been made, there are
> no further performance issues. Do you know if there is a fix, or have others
> experienced this slow performance after the upgrade?
> We have compared user desktops' memory, processors, hard drives, etc, and
> there is no trend there.
> We would appreciate any advice before moving forward w/ our other servers'
> upgrades!
Performance Hits - Second Post
Sorry for re-posting - wasn't sure how to -repost the same question. We are
becoming very frustrated with this performance issue with SQL server - any
response would be GREATLY appreciated.
We have recently upgraded 4 SQL servers to Windows 2003, running SQL Server
2000. Many people are complaining that Enterprise Manager now takes 4-20
seconds to expand the databases on those servers, where before it was either
instant or 1-2 seconds. Once the intial connection has been made, there are
no further performance issues. Do you know if there is a fix, or have others
experienced this slow performance after the upgrade?
We have compared user desktops' memory, processors, hard drives, etc, and
there is no trend there.
We would appreciate any advice before moving forward w/ our other servers'
upgrades!
Hi
How many databases are on the server?
Have you applied SQL Server SP3a to the clients also (EM and QA do get fixes
too)?
On a client machine, run in QA, "SELECT * FROM Master.dbo.sysdatabases" and
see how long it takes to return.
Regards
Mike
"JaneDoe" wrote:
> Sorry for re-posting - wasn't sure how to -repost the same question. We are
> becoming very frustrated with this performance issue with SQL server - any
> response would be GREATLY appreciated.
> We have recently upgraded 4 SQL servers to Windows 2003, running SQL Server
> 2000. Many people are complaining that Enterprise Manager now takes 4-20
> seconds to expand the databases on those servers, where before it was either
> instant or 1-2 seconds. Once the intial connection has been made, there are
> no further performance issues. Do you know if there is a fix, or have others
> experienced this slow performance after the upgrade?
> We have compared user desktops' memory, processors, hard drives, etc, and
> there is no trend there.
> We would appreciate any advice before moving forward w/ our other servers'
> upgrades!
>
|||Do you have Auto-Close turned on for those clients?
Andrew J. Kelly SQL MVP
"JaneDoe" <JaneDoe@.discussions.microsoft.com> wrote in message
news:10F5B1D4-84F8-4F1B-B419-26E82CEE8710@.microsoft.com...
> Sorry for re-posting - wasn't sure how to -repost the same question. We
> are
> becoming very frustrated with this performance issue with SQL server - any
> response would be GREATLY appreciated.
> We have recently upgraded 4 SQL servers to Windows 2003, running SQL
> Server
> 2000. Many people are complaining that Enterprise Manager now takes 4-20
> seconds to expand the databases on those servers, where before it was
> either
> instant or 1-2 seconds. Once the intial connection has been made, there
> are
> no further performance issues. Do you know if there is a fix, or have
> others
> experienced this slow performance after the upgrade?
> We have compared user desktops' memory, processors, hard drives, etc, and
> there is no trend there.
> We would appreciate any advice before moving forward w/ our other servers'
> upgrades!
>
|||Make sure you have SP3a applied to the client machines. See if these help:
http://support.microsoft.com/default...b;en-us;889696
http://support.microsoft.com/default...b;en-us;282416
Ryan Stonecipher
Microsoft Sql Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e0eCIEhCFHA.3416@.TK2MSFTNGP09.phx.gbl...
> Do you have Auto-Close turned on for those clients?
> --
> Andrew J. Kelly SQL MVP
>
> "JaneDoe" <JaneDoe@.discussions.microsoft.com> wrote in message
> news:10F5B1D4-84F8-4F1B-B419-26E82CEE8710@.microsoft.com...
>
becoming very frustrated with this performance issue with SQL server - any
response would be GREATLY appreciated.
We have recently upgraded 4 SQL servers to Windows 2003, running SQL Server
2000. Many people are complaining that Enterprise Manager now takes 4-20
seconds to expand the databases on those servers, where before it was either
instant or 1-2 seconds. Once the intial connection has been made, there are
no further performance issues. Do you know if there is a fix, or have others
experienced this slow performance after the upgrade?
We have compared user desktops' memory, processors, hard drives, etc, and
there is no trend there.
We would appreciate any advice before moving forward w/ our other servers'
upgrades!
Hi
How many databases are on the server?
Have you applied SQL Server SP3a to the clients also (EM and QA do get fixes
too)?
On a client machine, run in QA, "SELECT * FROM Master.dbo.sysdatabases" and
see how long it takes to return.
Regards
Mike
"JaneDoe" wrote:
> Sorry for re-posting - wasn't sure how to -repost the same question. We are
> becoming very frustrated with this performance issue with SQL server - any
> response would be GREATLY appreciated.
> We have recently upgraded 4 SQL servers to Windows 2003, running SQL Server
> 2000. Many people are complaining that Enterprise Manager now takes 4-20
> seconds to expand the databases on those servers, where before it was either
> instant or 1-2 seconds. Once the intial connection has been made, there are
> no further performance issues. Do you know if there is a fix, or have others
> experienced this slow performance after the upgrade?
> We have compared user desktops' memory, processors, hard drives, etc, and
> there is no trend there.
> We would appreciate any advice before moving forward w/ our other servers'
> upgrades!
>
|||Do you have Auto-Close turned on for those clients?
Andrew J. Kelly SQL MVP
"JaneDoe" <JaneDoe@.discussions.microsoft.com> wrote in message
news:10F5B1D4-84F8-4F1B-B419-26E82CEE8710@.microsoft.com...
> Sorry for re-posting - wasn't sure how to -repost the same question. We
> are
> becoming very frustrated with this performance issue with SQL server - any
> response would be GREATLY appreciated.
> We have recently upgraded 4 SQL servers to Windows 2003, running SQL
> Server
> 2000. Many people are complaining that Enterprise Manager now takes 4-20
> seconds to expand the databases on those servers, where before it was
> either
> instant or 1-2 seconds. Once the intial connection has been made, there
> are
> no further performance issues. Do you know if there is a fix, or have
> others
> experienced this slow performance after the upgrade?
> We have compared user desktops' memory, processors, hard drives, etc, and
> there is no trend there.
> We would appreciate any advice before moving forward w/ our other servers'
> upgrades!
>
|||Make sure you have SP3a applied to the client machines. See if these help:
http://support.microsoft.com/default...b;en-us;889696
http://support.microsoft.com/default...b;en-us;282416
Ryan Stonecipher
Microsoft Sql Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e0eCIEhCFHA.3416@.TK2MSFTNGP09.phx.gbl...
> Do you have Auto-Close turned on for those clients?
> --
> Andrew J. Kelly SQL MVP
>
> "JaneDoe" <JaneDoe@.discussions.microsoft.com> wrote in message
> news:10F5B1D4-84F8-4F1B-B419-26E82CEE8710@.microsoft.com...
>
Performance Hits - Second Post
Sorry for re-posting - wasn't sure how to -repost the same question. We are
becoming very frustrated with this performance issue with SQL server - any
response would be GREATLY appreciated.
We have recently upgraded 4 SQL servers to Windows 2003, running SQL Server
2000. Many people are complaining that Enterprise Manager now takes 4-20
seconds to expand the databases on those servers, where before it was either
instant or 1-2 seconds. Once the intial connection has been made, there are
no further performance issues. Do you know if there is a fix, or have other
s
experienced this slow performance after the upgrade?
We have compared user desktops' memory, processors, hard drives, etc, and
there is no trend there.
We would appreciate any advice before moving forward w/ our other servers'
upgrades!Hi
How many databases are on the server?
Have you applied SQL Server SP3a to the clients also (EM and QA do get fixes
too)?
On a client machine, run in QA, "SELECT * FROM Master.dbo.sysdatabases" and
see how long it takes to return.
Regards
Mike
"JaneDoe" wrote:
> Sorry for re-posting - wasn't sure how to -repost the same question. We a
re
> becoming very frustrated with this performance issue with SQL server - any
> response would be GREATLY appreciated.
> We have recently upgraded 4 SQL servers to Windows 2003, running SQL Serve
r
> 2000. Many people are complaining that Enterprise Manager now takes 4-20
> seconds to expand the databases on those servers, where before it was eith
er
> instant or 1-2 seconds. Once the intial connection has been made, there a
re
> no further performance issues. Do you know if there is a fix, or have oth
ers
> experienced this slow performance after the upgrade?
> We have compared user desktops' memory, processors, hard drives, etc, and
> there is no trend there.
> We would appreciate any advice before moving forward w/ our other servers'
> upgrades!
>|||Do you have Auto-Close turned on for those clients?
Andrew J. Kelly SQL MVP
"JaneDoe" <JaneDoe@.discussions.microsoft.com> wrote in message
news:10F5B1D4-84F8-4F1B-B419-26E82CEE8710@.microsoft.com...
> Sorry for re-posting - wasn't sure how to -repost the same question. We
> are
> becoming very frustrated with this performance issue with SQL server - any
> response would be GREATLY appreciated.
> We have recently upgraded 4 SQL servers to Windows 2003, running SQL
> Server
> 2000. Many people are complaining that Enterprise Manager now takes 4-20
> seconds to expand the databases on those servers, where before it was
> either
> instant or 1-2 seconds. Once the intial connection has been made, there
> are
> no further performance issues. Do you know if there is a fix, or have
> others
> experienced this slow performance after the upgrade?
> We have compared user desktops' memory, processors, hard drives, etc, and
> there is no trend there.
> We would appreciate any advice before moving forward w/ our other servers'
> upgrades!
>|||Make sure you have SP3a applied to the client machines. See if these help:
http://support.microsoft.com/defaul...kb;en-us;889696
http://support.microsoft.com/defaul...kb;en-us;282416
Ryan Stonecipher
Microsoft Sql Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e0eCIEhCFHA.3416@.TK2MSFTNGP09.phx.gbl...
> Do you have Auto-Close turned on for those clients?
> --
> Andrew J. Kelly SQL MVP
>
> "JaneDoe" <JaneDoe@.discussions.microsoft.com> wrote in message
> news:10F5B1D4-84F8-4F1B-B419-26E82CEE8710@.microsoft.com...
>
becoming very frustrated with this performance issue with SQL server - any
response would be GREATLY appreciated.
We have recently upgraded 4 SQL servers to Windows 2003, running SQL Server
2000. Many people are complaining that Enterprise Manager now takes 4-20
seconds to expand the databases on those servers, where before it was either
instant or 1-2 seconds. Once the intial connection has been made, there are
no further performance issues. Do you know if there is a fix, or have other
s
experienced this slow performance after the upgrade?
We have compared user desktops' memory, processors, hard drives, etc, and
there is no trend there.
We would appreciate any advice before moving forward w/ our other servers'
upgrades!Hi
How many databases are on the server?
Have you applied SQL Server SP3a to the clients also (EM and QA do get fixes
too)?
On a client machine, run in QA, "SELECT * FROM Master.dbo.sysdatabases" and
see how long it takes to return.
Regards
Mike
"JaneDoe" wrote:
> Sorry for re-posting - wasn't sure how to -repost the same question. We a
re
> becoming very frustrated with this performance issue with SQL server - any
> response would be GREATLY appreciated.
> We have recently upgraded 4 SQL servers to Windows 2003, running SQL Serve
r
> 2000. Many people are complaining that Enterprise Manager now takes 4-20
> seconds to expand the databases on those servers, where before it was eith
er
> instant or 1-2 seconds. Once the intial connection has been made, there a
re
> no further performance issues. Do you know if there is a fix, or have oth
ers
> experienced this slow performance after the upgrade?
> We have compared user desktops' memory, processors, hard drives, etc, and
> there is no trend there.
> We would appreciate any advice before moving forward w/ our other servers'
> upgrades!
>|||Do you have Auto-Close turned on for those clients?
Andrew J. Kelly SQL MVP
"JaneDoe" <JaneDoe@.discussions.microsoft.com> wrote in message
news:10F5B1D4-84F8-4F1B-B419-26E82CEE8710@.microsoft.com...
> Sorry for re-posting - wasn't sure how to -repost the same question. We
> are
> becoming very frustrated with this performance issue with SQL server - any
> response would be GREATLY appreciated.
> We have recently upgraded 4 SQL servers to Windows 2003, running SQL
> Server
> 2000. Many people are complaining that Enterprise Manager now takes 4-20
> seconds to expand the databases on those servers, where before it was
> either
> instant or 1-2 seconds. Once the intial connection has been made, there
> are
> no further performance issues. Do you know if there is a fix, or have
> others
> experienced this slow performance after the upgrade?
> We have compared user desktops' memory, processors, hard drives, etc, and
> there is no trend there.
> We would appreciate any advice before moving forward w/ our other servers'
> upgrades!
>|||Make sure you have SP3a applied to the client machines. See if these help:
http://support.microsoft.com/defaul...kb;en-us;889696
http://support.microsoft.com/defaul...kb;en-us;282416
Ryan Stonecipher
Microsoft Sql Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e0eCIEhCFHA.3416@.TK2MSFTNGP09.phx.gbl...
> Do you have Auto-Close turned on for those clients?
> --
> Andrew J. Kelly SQL MVP
>
> "JaneDoe" <JaneDoe@.discussions.microsoft.com> wrote in message
> news:10F5B1D4-84F8-4F1B-B419-26E82CEE8710@.microsoft.com...
>
Performance Hits - Second Post
Sorry for re-posting - wasn't sure how to -repost the same question. We are
becoming very frustrated with this performance issue with SQL server - any
response would be GREATLY appreciated.
We have recently upgraded 4 SQL servers to Windows 2003, running SQL Server
2000. Many people are complaining that Enterprise Manager now takes 4-20
seconds to expand the databases on those servers, where before it was either
instant or 1-2 seconds. Once the intial connection has been made, there are
no further performance issues. Do you know if there is a fix, or have others
experienced this slow performance after the upgrade?
We have compared user desktops' memory, processors, hard drives, etc, and
there is no trend there.
We would appreciate any advice before moving forward w/ our other servers'
upgrades!Hi
How many databases are on the server?
Have you applied SQL Server SP3a to the clients also (EM and QA do get fixes
too)?
On a client machine, run in QA, "SELECT * FROM Master.dbo.sysdatabases" and
see how long it takes to return.
Regards
Mike
"JaneDoe" wrote:
> Sorry for re-posting - wasn't sure how to -repost the same question. We are
> becoming very frustrated with this performance issue with SQL server - any
> response would be GREATLY appreciated.
> We have recently upgraded 4 SQL servers to Windows 2003, running SQL Server
> 2000. Many people are complaining that Enterprise Manager now takes 4-20
> seconds to expand the databases on those servers, where before it was either
> instant or 1-2 seconds. Once the intial connection has been made, there are
> no further performance issues. Do you know if there is a fix, or have others
> experienced this slow performance after the upgrade?
> We have compared user desktops' memory, processors, hard drives, etc, and
> there is no trend there.
> We would appreciate any advice before moving forward w/ our other servers'
> upgrades!
>|||Do you have Auto-Close turned on for those clients?
--
Andrew J. Kelly SQL MVP
"JaneDoe" <JaneDoe@.discussions.microsoft.com> wrote in message
news:10F5B1D4-84F8-4F1B-B419-26E82CEE8710@.microsoft.com...
> Sorry for re-posting - wasn't sure how to -repost the same question. We
> are
> becoming very frustrated with this performance issue with SQL server - any
> response would be GREATLY appreciated.
> We have recently upgraded 4 SQL servers to Windows 2003, running SQL
> Server
> 2000. Many people are complaining that Enterprise Manager now takes 4-20
> seconds to expand the databases on those servers, where before it was
> either
> instant or 1-2 seconds. Once the intial connection has been made, there
> are
> no further performance issues. Do you know if there is a fix, or have
> others
> experienced this slow performance after the upgrade?
> We have compared user desktops' memory, processors, hard drives, etc, and
> there is no trend there.
> We would appreciate any advice before moving forward w/ our other servers'
> upgrades!
>|||Make sure you have SP3a applied to the client machines. See if these help:
http://support.microsoft.com/default.aspx?scid=kb;en-us;889696
http://support.microsoft.com/default.aspx?scid=kb;en-us;282416
--
Ryan Stonecipher
Microsoft Sql Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e0eCIEhCFHA.3416@.TK2MSFTNGP09.phx.gbl...
> Do you have Auto-Close turned on for those clients?
> --
> Andrew J. Kelly SQL MVP
>
> "JaneDoe" <JaneDoe@.discussions.microsoft.com> wrote in message
> news:10F5B1D4-84F8-4F1B-B419-26E82CEE8710@.microsoft.com...
>> Sorry for re-posting - wasn't sure how to -repost the same question. We
>> are
>> becoming very frustrated with this performance issue with SQL server -
>> any
>> response would be GREATLY appreciated.
>> We have recently upgraded 4 SQL servers to Windows 2003, running SQL
>> Server
>> 2000. Many people are complaining that Enterprise Manager now takes 4-20
>> seconds to expand the databases on those servers, where before it was
>> either
>> instant or 1-2 seconds. Once the intial connection has been made, there
>> are
>> no further performance issues. Do you know if there is a fix, or have
>> others
>> experienced this slow performance after the upgrade?
>> We have compared user desktops' memory, processors, hard drives, etc, and
>> there is no trend there.
>> We would appreciate any advice before moving forward w/ our other
>> servers'
>> upgrades!
>
becoming very frustrated with this performance issue with SQL server - any
response would be GREATLY appreciated.
We have recently upgraded 4 SQL servers to Windows 2003, running SQL Server
2000. Many people are complaining that Enterprise Manager now takes 4-20
seconds to expand the databases on those servers, where before it was either
instant or 1-2 seconds. Once the intial connection has been made, there are
no further performance issues. Do you know if there is a fix, or have others
experienced this slow performance after the upgrade?
We have compared user desktops' memory, processors, hard drives, etc, and
there is no trend there.
We would appreciate any advice before moving forward w/ our other servers'
upgrades!Hi
How many databases are on the server?
Have you applied SQL Server SP3a to the clients also (EM and QA do get fixes
too)?
On a client machine, run in QA, "SELECT * FROM Master.dbo.sysdatabases" and
see how long it takes to return.
Regards
Mike
"JaneDoe" wrote:
> Sorry for re-posting - wasn't sure how to -repost the same question. We are
> becoming very frustrated with this performance issue with SQL server - any
> response would be GREATLY appreciated.
> We have recently upgraded 4 SQL servers to Windows 2003, running SQL Server
> 2000. Many people are complaining that Enterprise Manager now takes 4-20
> seconds to expand the databases on those servers, where before it was either
> instant or 1-2 seconds. Once the intial connection has been made, there are
> no further performance issues. Do you know if there is a fix, or have others
> experienced this slow performance after the upgrade?
> We have compared user desktops' memory, processors, hard drives, etc, and
> there is no trend there.
> We would appreciate any advice before moving forward w/ our other servers'
> upgrades!
>|||Do you have Auto-Close turned on for those clients?
--
Andrew J. Kelly SQL MVP
"JaneDoe" <JaneDoe@.discussions.microsoft.com> wrote in message
news:10F5B1D4-84F8-4F1B-B419-26E82CEE8710@.microsoft.com...
> Sorry for re-posting - wasn't sure how to -repost the same question. We
> are
> becoming very frustrated with this performance issue with SQL server - any
> response would be GREATLY appreciated.
> We have recently upgraded 4 SQL servers to Windows 2003, running SQL
> Server
> 2000. Many people are complaining that Enterprise Manager now takes 4-20
> seconds to expand the databases on those servers, where before it was
> either
> instant or 1-2 seconds. Once the intial connection has been made, there
> are
> no further performance issues. Do you know if there is a fix, or have
> others
> experienced this slow performance after the upgrade?
> We have compared user desktops' memory, processors, hard drives, etc, and
> there is no trend there.
> We would appreciate any advice before moving forward w/ our other servers'
> upgrades!
>|||Make sure you have SP3a applied to the client machines. See if these help:
http://support.microsoft.com/default.aspx?scid=kb;en-us;889696
http://support.microsoft.com/default.aspx?scid=kb;en-us;282416
--
Ryan Stonecipher
Microsoft Sql Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e0eCIEhCFHA.3416@.TK2MSFTNGP09.phx.gbl...
> Do you have Auto-Close turned on for those clients?
> --
> Andrew J. Kelly SQL MVP
>
> "JaneDoe" <JaneDoe@.discussions.microsoft.com> wrote in message
> news:10F5B1D4-84F8-4F1B-B419-26E82CEE8710@.microsoft.com...
>> Sorry for re-posting - wasn't sure how to -repost the same question. We
>> are
>> becoming very frustrated with this performance issue with SQL server -
>> any
>> response would be GREATLY appreciated.
>> We have recently upgraded 4 SQL servers to Windows 2003, running SQL
>> Server
>> 2000. Many people are complaining that Enterprise Manager now takes 4-20
>> seconds to expand the databases on those servers, where before it was
>> either
>> instant or 1-2 seconds. Once the intial connection has been made, there
>> are
>> no further performance issues. Do you know if there is a fix, or have
>> others
>> experienced this slow performance after the upgrade?
>> We have compared user desktops' memory, processors, hard drives, etc, and
>> there is no trend there.
>> We would appreciate any advice before moving forward w/ our other
>> servers'
>> upgrades!
>
Wednesday, March 28, 2012
Performance different
I have 2 databases that are identical in schema and structure, one hold June
data and one hold July data. I ran the query that hits 4 tables. Tables on
both databases are identical including indexes and keys. It took 40 seconds
when I run under June database. When I run under July, it did not return
the result set and it been running more than 2 hours. I checked the number
of data on those 4 tables and they are basically the same. Is there a way
for me to know what is going on? I used Profiler but no help. The query is
single commit transaction. Please help. Thanks!Your most likely blocked. Run sp_who2 to see who is blocking you.
--
Andrew J. Kelly
SQL Server MVP
"Kevin" <kevin@.noemail.com> wrote in message
news:eDLkUUJrDHA.2632@.TK2MSFTNGP09.phx.gbl...
> I have 2 databases that are identical in schema and structure, one hold
June
> data and one hold July data. I ran the query that hits 4 tables. Tables
on
> both databases are identical including indexes and keys. It took 40
seconds
> when I run under June database. When I run under July, it did not return
> the result set and it been running more than 2 hours. I checked the
number
> of data on those 4 tables and they are basically the same. Is there a way
> for me to know what is going on? I used Profiler but no help. The query
is
> single commit transaction. Please help. Thanks!
>|||I ran sp_lock & sp_who2 and found no exclusive locks. The only locks I
found was shared locks. This is for the data warehouse environment and we
only have select statments. Thanks for the recommendation.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O1RyAzJrDHA.3320@.tk2msftngp13.phx.gbl...
> Your most likely blocked. Run sp_who2 to see who is blocking you.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Kevin" <kevin@.noemail.com> wrote in message
> news:eDLkUUJrDHA.2632@.TK2MSFTNGP09.phx.gbl...
> > I have 2 databases that are identical in schema and structure, one hold
> June
> > data and one hold July data. I ran the query that hits 4 tables.
Tables
> on
> > both databases are identical including indexes and keys. It took 40
> seconds
> > when I run under June database. When I run under July, it did not
return
> > the result set and it been running more than 2 hours. I checked the
> number
> > of data on those 4 tables and they are basically the same. Is there a
way
> > for me to know what is going on? I used Profiler but no help. The
query
> is
> > single commit transaction. Please help. Thanks!
> >
> >
>|||Is the estimated query plan the same for both? What is the status of the
spid while it is running? Is there activity going on (disk, cpu etc)?
--
Andrew J. Kelly
SQL Server MVP
"Kevin" <kevin@.noemail.com> wrote in message
news:OkdUVCUrDHA.2500@.TK2MSFTNGP10.phx.gbl...
> I ran sp_lock & sp_who2 and found no exclusive locks. The only locks I
> found was shared locks. This is for the data warehouse environment and we
> only have select statments. Thanks for the recommendation.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:O1RyAzJrDHA.3320@.tk2msftngp13.phx.gbl...
> > Your most likely blocked. Run sp_who2 to see who is blocking you.
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "Kevin" <kevin@.noemail.com> wrote in message
> > news:eDLkUUJrDHA.2632@.TK2MSFTNGP09.phx.gbl...
> > > I have 2 databases that are identical in schema and structure, one
hold
> > June
> > > data and one hold July data. I ran the query that hits 4 tables.
> Tables
> > on
> > > both databases are identical including indexes and keys. It took 40
> > seconds
> > > when I run under June database. When I run under July, it did not
> return
> > > the result set and it been running more than 2 hours. I checked the
> > number
> > > of data on those 4 tables and they are basically the same. Is there a
> way
> > > for me to know what is going on? I used Profiler but no help. The
> query
> > is
> > > single commit transaction. Please help. Thanks!
> > >
> > >
> >
> >
>|||I was not able to see the execution plan for the slow one because it didn't
stop. But the query structure, tables, indexes are identical. The SPID for
both are SELECT and using parallelism. The CPU for both are spiking, disk
write/sec, page write/sec, data map hits, and lazy write pages/sec are at
constant flat lines.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eIDOVpUrDHA.4004@.TK2MSFTNGP11.phx.gbl...
> Is the estimated query plan the same for both? What is the status of the
> spid while it is running? Is there activity going on (disk, cpu etc)?
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Kevin" <kevin@.noemail.com> wrote in message
> news:OkdUVCUrDHA.2500@.TK2MSFTNGP10.phx.gbl...
> > I ran sp_lock & sp_who2 and found no exclusive locks. The only locks I
> > found was shared locks. This is for the data warehouse environment and
we
> > only have select statments. Thanks for the recommendation.
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:O1RyAzJrDHA.3320@.tk2msftngp13.phx.gbl...
> > > Your most likely blocked. Run sp_who2 to see who is blocking you.
> > >
> > > --
> > >
> > > Andrew J. Kelly
> > > SQL Server MVP
> > >
> > >
> > > "Kevin" <kevin@.noemail.com> wrote in message
> > > news:eDLkUUJrDHA.2632@.TK2MSFTNGP09.phx.gbl...
> > > > I have 2 databases that are identical in schema and structure, one
> hold
> > > June
> > > > data and one hold July data. I ran the query that hits 4 tables.
> > Tables
> > > on
> > > > both databases are identical including indexes and keys. It took 40
> > > seconds
> > > > when I run under June database. When I run under July, it did not
> > return
> > > > the result set and it been running more than 2 hours. I checked the
> > > number
> > > > of data on those 4 tables and they are basically the same. Is there
a
> > way
> > > > for me to know what is going on? I used Profiler but no help. The
> > query
> > > is
> > > > single commit transaction. Please help. Thanks!
> > > >
> > > >
> > >
> > >
> >
> >
>|||Kevin,
You can do an Estimated query plan without actually running the query. In
Query Analyzer you can highlight the query and press Ctrl + L to see it. By
"all flat lines" do you mean maxed out? If so then your are most likely
doing a full table scan. The estimated query plan will tell you.
--
Andrew J. Kelly
SQL Server MVP
"Kevin" <kevin@.noemail.com> wrote in message
news:OHKakiXrDHA.2808@.TK2MSFTNGP10.phx.gbl...
> I was not able to see the execution plan for the slow one because it
didn't
> stop. But the query structure, tables, indexes are identical. The SPID
for
> both are SELECT and using parallelism. The CPU for both are spiking, disk
> write/sec, page write/sec, data map hits, and lazy write pages/sec are at
> constant flat lines.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eIDOVpUrDHA.4004@.TK2MSFTNGP11.phx.gbl...
> > Is the estimated query plan the same for both? What is the status of
the
> > spid while it is running? Is there activity going on (disk, cpu etc)?
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "Kevin" <kevin@.noemail.com> wrote in message
> > news:OkdUVCUrDHA.2500@.TK2MSFTNGP10.phx.gbl...
> > > I ran sp_lock & sp_who2 and found no exclusive locks. The only locks
I
> > > found was shared locks. This is for the data warehouse environment
and
> we
> > > only have select statments. Thanks for the recommendation.
> > >
> > > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > > news:O1RyAzJrDHA.3320@.tk2msftngp13.phx.gbl...
> > > > Your most likely blocked. Run sp_who2 to see who is blocking you.
> > > >
> > > > --
> > > >
> > > > Andrew J. Kelly
> > > > SQL Server MVP
> > > >
> > > >
> > > > "Kevin" <kevin@.noemail.com> wrote in message
> > > > news:eDLkUUJrDHA.2632@.TK2MSFTNGP09.phx.gbl...
> > > > > I have 2 databases that are identical in schema and structure, one
> > hold
> > > > June
> > > > > data and one hold July data. I ran the query that hits 4 tables.
> > > Tables
> > > > on
> > > > > both databases are identical including indexes and keys. It took
40
> > > > seconds
> > > > > when I run under June database. When I run under July, it did not
> > > return
> > > > > the result set and it been running more than 2 hours. I checked
the
> > > > number
> > > > > of data on those 4 tables and they are basically the same. Is
there
> a
> > > way
> > > > > for me to know what is going on? I used Profiler but no help.
The
> > > query
> > > > is
> > > > > single commit transaction. Please help. Thanks!
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Thank you for your suggestion. After figuring what the Estimated query plan
does, it is caused by recursive loops. One month return 2,500 rows while
the other one return 15,000. These recursively 3 times and that make the
query run on 2nd database longer. I was able fix the query so it run
faster.
THANK YOU Andrew for your help !!!!!
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ufQspyXrDHA.2964@.tk2msftngp13.phx.gbl...
> Kevin,
> You can do an Estimated query plan without actually running the query. In
> Query Analyzer you can highlight the query and press Ctrl + L to see it.
By
> "all flat lines" do you mean maxed out? If so then your are most likely
> doing a full table scan. The estimated query plan will tell you.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Kevin" <kevin@.noemail.com> wrote in message
> news:OHKakiXrDHA.2808@.TK2MSFTNGP10.phx.gbl...
> > I was not able to see the execution plan for the slow one because it
> didn't
> > stop. But the query structure, tables, indexes are identical. The SPID
> for
> > both are SELECT and using parallelism. The CPU for both are spiking,
disk
> > write/sec, page write/sec, data map hits, and lazy write pages/sec are
at
> > constant flat lines.
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:eIDOVpUrDHA.4004@.TK2MSFTNGP11.phx.gbl...
> > > Is the estimated query plan the same for both? What is the status of
> the
> > > spid while it is running? Is there activity going on (disk, cpu etc)?
> > >
> > > --
> > >
> > > Andrew J. Kelly
> > > SQL Server MVP
> > >
> > >
> > > "Kevin" <kevin@.noemail.com> wrote in message
> > > news:OkdUVCUrDHA.2500@.TK2MSFTNGP10.phx.gbl...
> > > > I ran sp_lock & sp_who2 and found no exclusive locks. The only
locks
> I
> > > > found was shared locks. This is for the data warehouse environment
> and
> > we
> > > > only have select statments. Thanks for the recommendation.
> > > >
> > > > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > > > news:O1RyAzJrDHA.3320@.tk2msftngp13.phx.gbl...
> > > > > Your most likely blocked. Run sp_who2 to see who is blocking you.
> > > > >
> > > > > --
> > > > >
> > > > > Andrew J. Kelly
> > > > > SQL Server MVP
> > > > >
> > > > >
> > > > > "Kevin" <kevin@.noemail.com> wrote in message
> > > > > news:eDLkUUJrDHA.2632@.TK2MSFTNGP09.phx.gbl...
> > > > > > I have 2 databases that are identical in schema and structure,
one
> > > hold
> > > > > June
> > > > > > data and one hold July data. I ran the query that hits 4
tables.
> > > > Tables
> > > > > on
> > > > > > both databases are identical including indexes and keys. It
took
> 40
> > > > > seconds
> > > > > > when I run under June database. When I run under July, it did
not
> > > > return
> > > > > > the result set and it been running more than 2 hours. I checked
> the
> > > > > number
> > > > > > of data on those 4 tables and they are basically the same. Is
> there
> > a
> > > > way
> > > > > > for me to know what is going on? I used Profiler but no help.
> The
> > > > query
> > > > > is
> > > > > > single commit transaction. Please help. Thanks!
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
data and one hold July data. I ran the query that hits 4 tables. Tables on
both databases are identical including indexes and keys. It took 40 seconds
when I run under June database. When I run under July, it did not return
the result set and it been running more than 2 hours. I checked the number
of data on those 4 tables and they are basically the same. Is there a way
for me to know what is going on? I used Profiler but no help. The query is
single commit transaction. Please help. Thanks!Your most likely blocked. Run sp_who2 to see who is blocking you.
--
Andrew J. Kelly
SQL Server MVP
"Kevin" <kevin@.noemail.com> wrote in message
news:eDLkUUJrDHA.2632@.TK2MSFTNGP09.phx.gbl...
> I have 2 databases that are identical in schema and structure, one hold
June
> data and one hold July data. I ran the query that hits 4 tables. Tables
on
> both databases are identical including indexes and keys. It took 40
seconds
> when I run under June database. When I run under July, it did not return
> the result set and it been running more than 2 hours. I checked the
number
> of data on those 4 tables and they are basically the same. Is there a way
> for me to know what is going on? I used Profiler but no help. The query
is
> single commit transaction. Please help. Thanks!
>|||I ran sp_lock & sp_who2 and found no exclusive locks. The only locks I
found was shared locks. This is for the data warehouse environment and we
only have select statments. Thanks for the recommendation.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O1RyAzJrDHA.3320@.tk2msftngp13.phx.gbl...
> Your most likely blocked. Run sp_who2 to see who is blocking you.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Kevin" <kevin@.noemail.com> wrote in message
> news:eDLkUUJrDHA.2632@.TK2MSFTNGP09.phx.gbl...
> > I have 2 databases that are identical in schema and structure, one hold
> June
> > data and one hold July data. I ran the query that hits 4 tables.
Tables
> on
> > both databases are identical including indexes and keys. It took 40
> seconds
> > when I run under June database. When I run under July, it did not
return
> > the result set and it been running more than 2 hours. I checked the
> number
> > of data on those 4 tables and they are basically the same. Is there a
way
> > for me to know what is going on? I used Profiler but no help. The
query
> is
> > single commit transaction. Please help. Thanks!
> >
> >
>|||Is the estimated query plan the same for both? What is the status of the
spid while it is running? Is there activity going on (disk, cpu etc)?
--
Andrew J. Kelly
SQL Server MVP
"Kevin" <kevin@.noemail.com> wrote in message
news:OkdUVCUrDHA.2500@.TK2MSFTNGP10.phx.gbl...
> I ran sp_lock & sp_who2 and found no exclusive locks. The only locks I
> found was shared locks. This is for the data warehouse environment and we
> only have select statments. Thanks for the recommendation.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:O1RyAzJrDHA.3320@.tk2msftngp13.phx.gbl...
> > Your most likely blocked. Run sp_who2 to see who is blocking you.
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "Kevin" <kevin@.noemail.com> wrote in message
> > news:eDLkUUJrDHA.2632@.TK2MSFTNGP09.phx.gbl...
> > > I have 2 databases that are identical in schema and structure, one
hold
> > June
> > > data and one hold July data. I ran the query that hits 4 tables.
> Tables
> > on
> > > both databases are identical including indexes and keys. It took 40
> > seconds
> > > when I run under June database. When I run under July, it did not
> return
> > > the result set and it been running more than 2 hours. I checked the
> > number
> > > of data on those 4 tables and they are basically the same. Is there a
> way
> > > for me to know what is going on? I used Profiler but no help. The
> query
> > is
> > > single commit transaction. Please help. Thanks!
> > >
> > >
> >
> >
>|||I was not able to see the execution plan for the slow one because it didn't
stop. But the query structure, tables, indexes are identical. The SPID for
both are SELECT and using parallelism. The CPU for both are spiking, disk
write/sec, page write/sec, data map hits, and lazy write pages/sec are at
constant flat lines.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eIDOVpUrDHA.4004@.TK2MSFTNGP11.phx.gbl...
> Is the estimated query plan the same for both? What is the status of the
> spid while it is running? Is there activity going on (disk, cpu etc)?
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Kevin" <kevin@.noemail.com> wrote in message
> news:OkdUVCUrDHA.2500@.TK2MSFTNGP10.phx.gbl...
> > I ran sp_lock & sp_who2 and found no exclusive locks. The only locks I
> > found was shared locks. This is for the data warehouse environment and
we
> > only have select statments. Thanks for the recommendation.
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:O1RyAzJrDHA.3320@.tk2msftngp13.phx.gbl...
> > > Your most likely blocked. Run sp_who2 to see who is blocking you.
> > >
> > > --
> > >
> > > Andrew J. Kelly
> > > SQL Server MVP
> > >
> > >
> > > "Kevin" <kevin@.noemail.com> wrote in message
> > > news:eDLkUUJrDHA.2632@.TK2MSFTNGP09.phx.gbl...
> > > > I have 2 databases that are identical in schema and structure, one
> hold
> > > June
> > > > data and one hold July data. I ran the query that hits 4 tables.
> > Tables
> > > on
> > > > both databases are identical including indexes and keys. It took 40
> > > seconds
> > > > when I run under June database. When I run under July, it did not
> > return
> > > > the result set and it been running more than 2 hours. I checked the
> > > number
> > > > of data on those 4 tables and they are basically the same. Is there
a
> > way
> > > > for me to know what is going on? I used Profiler but no help. The
> > query
> > > is
> > > > single commit transaction. Please help. Thanks!
> > > >
> > > >
> > >
> > >
> >
> >
>|||Kevin,
You can do an Estimated query plan without actually running the query. In
Query Analyzer you can highlight the query and press Ctrl + L to see it. By
"all flat lines" do you mean maxed out? If so then your are most likely
doing a full table scan. The estimated query plan will tell you.
--
Andrew J. Kelly
SQL Server MVP
"Kevin" <kevin@.noemail.com> wrote in message
news:OHKakiXrDHA.2808@.TK2MSFTNGP10.phx.gbl...
> I was not able to see the execution plan for the slow one because it
didn't
> stop. But the query structure, tables, indexes are identical. The SPID
for
> both are SELECT and using parallelism. The CPU for both are spiking, disk
> write/sec, page write/sec, data map hits, and lazy write pages/sec are at
> constant flat lines.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eIDOVpUrDHA.4004@.TK2MSFTNGP11.phx.gbl...
> > Is the estimated query plan the same for both? What is the status of
the
> > spid while it is running? Is there activity going on (disk, cpu etc)?
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "Kevin" <kevin@.noemail.com> wrote in message
> > news:OkdUVCUrDHA.2500@.TK2MSFTNGP10.phx.gbl...
> > > I ran sp_lock & sp_who2 and found no exclusive locks. The only locks
I
> > > found was shared locks. This is for the data warehouse environment
and
> we
> > > only have select statments. Thanks for the recommendation.
> > >
> > > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > > news:O1RyAzJrDHA.3320@.tk2msftngp13.phx.gbl...
> > > > Your most likely blocked. Run sp_who2 to see who is blocking you.
> > > >
> > > > --
> > > >
> > > > Andrew J. Kelly
> > > > SQL Server MVP
> > > >
> > > >
> > > > "Kevin" <kevin@.noemail.com> wrote in message
> > > > news:eDLkUUJrDHA.2632@.TK2MSFTNGP09.phx.gbl...
> > > > > I have 2 databases that are identical in schema and structure, one
> > hold
> > > > June
> > > > > data and one hold July data. I ran the query that hits 4 tables.
> > > Tables
> > > > on
> > > > > both databases are identical including indexes and keys. It took
40
> > > > seconds
> > > > > when I run under June database. When I run under July, it did not
> > > return
> > > > > the result set and it been running more than 2 hours. I checked
the
> > > > number
> > > > > of data on those 4 tables and they are basically the same. Is
there
> a
> > > way
> > > > > for me to know what is going on? I used Profiler but no help.
The
> > > query
> > > > is
> > > > > single commit transaction. Please help. Thanks!
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Thank you for your suggestion. After figuring what the Estimated query plan
does, it is caused by recursive loops. One month return 2,500 rows while
the other one return 15,000. These recursively 3 times and that make the
query run on 2nd database longer. I was able fix the query so it run
faster.
THANK YOU Andrew for your help !!!!!
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ufQspyXrDHA.2964@.tk2msftngp13.phx.gbl...
> Kevin,
> You can do an Estimated query plan without actually running the query. In
> Query Analyzer you can highlight the query and press Ctrl + L to see it.
By
> "all flat lines" do you mean maxed out? If so then your are most likely
> doing a full table scan. The estimated query plan will tell you.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Kevin" <kevin@.noemail.com> wrote in message
> news:OHKakiXrDHA.2808@.TK2MSFTNGP10.phx.gbl...
> > I was not able to see the execution plan for the slow one because it
> didn't
> > stop. But the query structure, tables, indexes are identical. The SPID
> for
> > both are SELECT and using parallelism. The CPU for both are spiking,
disk
> > write/sec, page write/sec, data map hits, and lazy write pages/sec are
at
> > constant flat lines.
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:eIDOVpUrDHA.4004@.TK2MSFTNGP11.phx.gbl...
> > > Is the estimated query plan the same for both? What is the status of
> the
> > > spid while it is running? Is there activity going on (disk, cpu etc)?
> > >
> > > --
> > >
> > > Andrew J. Kelly
> > > SQL Server MVP
> > >
> > >
> > > "Kevin" <kevin@.noemail.com> wrote in message
> > > news:OkdUVCUrDHA.2500@.TK2MSFTNGP10.phx.gbl...
> > > > I ran sp_lock & sp_who2 and found no exclusive locks. The only
locks
> I
> > > > found was shared locks. This is for the data warehouse environment
> and
> > we
> > > > only have select statments. Thanks for the recommendation.
> > > >
> > > > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > > > news:O1RyAzJrDHA.3320@.tk2msftngp13.phx.gbl...
> > > > > Your most likely blocked. Run sp_who2 to see who is blocking you.
> > > > >
> > > > > --
> > > > >
> > > > > Andrew J. Kelly
> > > > > SQL Server MVP
> > > > >
> > > > >
> > > > > "Kevin" <kevin@.noemail.com> wrote in message
> > > > > news:eDLkUUJrDHA.2632@.TK2MSFTNGP09.phx.gbl...
> > > > > > I have 2 databases that are identical in schema and structure,
one
> > > hold
> > > > > June
> > > > > > data and one hold July data. I ran the query that hits 4
tables.
> > > > Tables
> > > > > on
> > > > > > both databases are identical including indexes and keys. It
took
> 40
> > > > > seconds
> > > > > > when I run under June database. When I run under July, it did
not
> > > > return
> > > > > > the result set and it been running more than 2 hours. I checked
> the
> > > > > number
> > > > > > of data on those 4 tables and they are basically the same. Is
> there
> > a
> > > > way
> > > > > > for me to know what is going on? I used Profiler but no help.
> The
> > > > query
> > > > > is
> > > > > > single commit transaction. Please help. Thanks!
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
Subscribe to:
Posts (Atom)