performance monitoring: February 2012

Saturday, February 25, 2012

Performance of "text" versus "image" datatypes (SS2000)

In terms of simple insertion and selection, is there any appreciable performance difference between text and image? (assuming the size of the data in bytes would be roughly equal)

Please also assme we're storing all LOBs out of row.

Thanks!

It would be the same. However, if your lob is nothing more than some very long string, putting it 'text' column would be a better fit.

Performance of "SELECT COUNT(*) FROM X WHERE Y" queries

I have a web application which has queues with different views for different
users. The site has several hundred users a day. With enough RAM in the
server (the application effectively living in RAM) is it quite reasonable
and ok performance wise to be able to use "SELECT COUNT(*) FROM X WHERE Y"
queries (inside an SP) to generate the counters on each page load or should
you use a trigger or perhaps another approach - like a middle tier component
to cache the values?
Thanks
RichardPerformance of a SELECT COUNT(*) query is depends largely on available
indexes. In you example, a non-clustered index on Y will provide an
efficient method to get the count and performance will be roughly
proportional to the number of qualifying rows.
I suggest you run a simple performance test with your worst-case scenario.
If response time is acceptable, you're done. It's best not to implement
techniques like caching or redundant data to address a performance problem
you don't have.
Hope this helps.
Dan Guzman
SQL Server MVP
"Richard Hollis" <richard_hollis@.hotmail.com> wrote in message
news:%23W2x5UEZFHA.2996@.TK2MSFTNGP10.phx.gbl...
>I have a web application which has queues with different views for
>different
> users. The site has several hundred users a day. With enough RAM in the
> server (the application effectively living in RAM) is it quite reasonable
> and ok performance wise to be able to use "SELECT COUNT(*) FROM X WHERE Y"
> queries (inside an SP) to generate the counters on each page load or
> should
> you use a trigger or perhaps another approach - like a middle tier
> component
> to cache the values?
> Thanks
> Richard
>

performance objects for SQLServer disappeared

Using SS2000. I have been several performance monitor counters for SQLServer.
When I tried to start the counters this morning, I received this error:
The database_size log or alert has not started.
When I tried to look at the counter, under performance object there are no
objects for SQLServer.
Does anyone know why or how these could disappear? SQL Server is still
running on the box.
Thanks,
Dan D.
Have a look at this:
http://support.microsoft.com/default...&Product=sql2k
Andrew J. Kelly SQL MVP
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:68581C5F-771A-47D6-8B75-180D4B23D5AF@.microsoft.com...
> Using SS2000. I have been several performance monitor counters for
SQLServer.
> When I tried to start the counters this morning, I received this error:
> The database_size log or alert has not started.
> When I tried to look at the counter, under performance object there are no
> objects for SQLServer.
> Does anyone know why or how these could disappear? SQL Server is still
> running on the box.
> Thanks,
> --
> Dan D.
|||Thanks Andrew. That looks like it may be it.
"Andrew J. Kelly" wrote:

> Have a look at this:
> http://support.microsoft.com/default...&Product=sql2k
> --
> Andrew J. Kelly SQL MVP
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:68581C5F-771A-47D6-8B75-180D4B23D5AF@.microsoft.com...
> SQLServer.
>
>

performance objects for SQLServer disappeared

Using SS2000. I have been several performance monitor counters for SQLServer.
When I tried to start the counters this morning, I received this error:
The database_size log or alert has not started.
When I tried to look at the counter, under performance object there are no
objects for SQLServer.
Does anyone know why or how these could disappear? SQL Server is still
running on the box.
Thanks,
--
Dan D.Have a look at this:
http://support.microsoft.com/default.aspx?scid=kb;en-us;812915&Product=sql2k
--
Andrew J. Kelly SQL MVP
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:68581C5F-771A-47D6-8B75-180D4B23D5AF@.microsoft.com...
> Using SS2000. I have been several performance monitor counters for
SQLServer.
> When I tried to start the counters this morning, I received this error:
> The database_size log or alert has not started.
> When I tried to look at the counter, under performance object there are no
> objects for SQLServer.
> Does anyone know why or how these could disappear? SQL Server is still
> running on the box.
> Thanks,
> --
> Dan D.

Performance Object SQL Server

Bart
I'm nbot sure uinderstand you. Click Start --Run --perfmon.
On the sceen right click and Add counters. There is Perfomance Object
combobox where you can choose many counters for SQL Server as well
"Bart Steur" <solnews@.xs4all.nl> wrote in message
news:%23EaGga8PHHA.404@.TK2MSFTNGP02.phx.gbl...
> Hello
> We are running W2003 Server with SQL Server 2000. Now I want to monitor
> performance using the administrative tool Performance.
> According to the SQL Server 2000 System Administration (Chapter 14), there
> should be several SQL Server Performance Objects to track SQL server
> activity, but...
> I can't find them. What happend, where are they.
> Anyone?
> Thanks,
> Bart
>
Bart
http://support.microsoft.com/kb/227662
http://support.microsoft.com/kb/246328
"Bart Steur" <solnews@.xs4all.nl> wrote in message
news:uJzNb68PHHA.2312@.TK2MSFTNGP04.phx.gbl...
> Some Extra information (Had a chat with our System Administrator).
> The W2003 Server runs 2 SQL servers. 1 SQL Server 2000 and another that
> came with our backup program (BACKUPEXEC) to store backup information. The
> last one I can see in the Performance Monitor. But that's not the one I
> would like to monitor. Any idea how to get the objects of the other SQL
> server into the performance monitor, or maybe how to swap them.
> thanks
> Bart
>
|||Bart
You are right, my mistake, sorry
For detailed information, please reference the following article:
330088 BUG: SQLServer:Databases Performance Counters Limited to First 99
http://support.microsoft.com/?id=330088
SQL Server 2000 Performance Counters Do Not Appear in the Performance
http://support.microsoft.com/?id=827260
http://support.microsoft.com/?id=812915
"Bart Steur" <solnews@.xs4all.nl> wrote in message
news:u5fIPIGQHHA.2468@.TK2MSFTNGP06.phx.gbl...
> Those articles do not apply to SQLServer 2000.
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eXynY$8PHHA.5012@.TK2MSFTNGP02.phx.gbl...
>
|||Bart

> By the way, the reason I'm so eager to see the performance, is because we
> plugged in an additional 2GB (making it 4GB) and I can't see any
> difference in (heavy) query speeds. So I lookup the tuning part in the
> SQLS2000 System Administration book and that talked about the perfmon.
I have my doubt that a 'bad' written query will perform better if you add
more memory. I'd run SQL Server Profiler to identify long running queries
and start tunning them
"Bart Steur" <solnews@.xs4all.nl> wrote in message
news:uWovibGQHHA.3624@.TK2MSFTNGP05.phx.gbl...
> Thanks Uri, but none of the provided solutions helped.
> Maby it is because the SQL Server used by our Backup Program (BACKUPEXEC)
> is a DESKTOP version.
> The 'real' SQL Server used for our data is an ENTERPRISE edition.
> FYI. I'm not interested in the performance of the DESKTOP version. So
> those may be eliminated from the Perfmon.
> By the way, the reason I'm so eager to see the performance, is because we
> plugged in an additional 2GB (making it 4GB) and I can't see any
> difference in (heavy) query speeds. So I lookup the tuning part in the
> SQLS2000 System Administration book and that talked about the perfmon.
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:udOdfOGQHHA.4172@.TK2MSFTNGP04.phx.gbl...
>
|||Hello Bart,
Can you pl. check the AWE option?
http://msdn2.microsoft.com/en-us/library/aa213764(SQL.80).aspx
Cheers,
MB
"Bart Steur" <solnews@.xs4all.nl> wrote in message
news:uWovibGQHHA.3624@.TK2MSFTNGP05.phx.gbl...
> Thanks Uri, but none of the provided solutions helped.
> Maby it is because the SQL Server used by our Backup Program (BACKUPEXEC)
> is a DESKTOP version.
> The 'real' SQL Server used for our data is an ENTERPRISE edition.
> FYI. I'm not interested in the performance of the DESKTOP version. So
> those may be eliminated from the Perfmon.
> By the way, the reason I'm so eager to see the performance, is because we
> plugged in an additional 2GB (making it 4GB) and I can't see any
> difference in (heavy) query speeds. So I lookup the tuning part in the
> SQLS2000 System Administration book and that talked about the perfmon.
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:udOdfOGQHHA.4172@.TK2MSFTNGP04.phx.gbl...
>
|||Try here: http://www.extremeexperts.com/SQL/FAQ/EnablingPerCounters.aspx
John
Bart Steur wrote:

> I've looked into this, but I don't think this option applies to my
> situation. First we are using Window 2003 Server Standard, and second this
> is for very large memory sizes. We only have 4 GB. But thanks for the
> support. Any other suggestions are welcome.
> Regards
> Bart
> "MB" <MB@.MB.com> wrote in message
> news:uedn1jGQHHA.4172@.TK2MSFTNGP04.phx.gbl...
>
>
|||You're welcome.
John
Bart Steur wrote:
> Works like a charm. Thanks John John
> "John John" <audetweld@.nbnet.nb.ca> wrote in message
> news:OdafzcHQHHA.1380@.TK2MSFTNGP05.phx.gbl...
>

Performance Object SQL Server

Hello
We are running W2003 Server with SQL Server 2000. Now I want to monitor
performance using the administrative tool Performance.
According to the SQL Server 2000 System Administration (Chapter 14), there
should be several SQL Server Performance Objects to track SQL server
activity, but...
I can't find them. What happend, where are they.
Anyone?
Thanks,
BartBart
I'm nbot sure uinderstand you. Click Start --Run --perfmon.
On the sceen right click and Add counters. There is Perfomance Object
combobox where you can choose many counters for SQL Server as well
"Bart Steur" <solnews@.xs4all.nl> wrote in message
news:%23EaGga8PHHA.404@.TK2MSFTNGP02.phx.gbl...
> Hello
> We are running W2003 Server with SQL Server 2000. Now I want to monitor
> performance using the administrative tool Performance.
> According to the SQL Server 2000 System Administration (Chapter 14), there
> should be several SQL Server Performance Objects to track SQL server
> activity, but...
> I can't find them. What happend, where are they.
> Anyone?
> Thanks,
> Bart
>|||Thats the problem, It should be there but it isn't.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OGHpRg8PHHA.3544@.TK2MSFTNGP03.phx.gbl...
> Bart
> I'm nbot sure uinderstand you. Click Start --Run --perfmon.
> On the sceen right click and Add counters. There is Perfomance Object
> combobox where you can choose many counters for SQL Server as well
>
>
>
>
> "Bart Steur" <solnews@.xs4all.nl> wrote in message
> news:%23EaGga8PHHA.404@.TK2MSFTNGP02.phx.gbl...
>> Hello
>> We are running W2003 Server with SQL Server 2000. Now I want to monitor
>> performance using the administrative tool Performance.
>> According to the SQL Server 2000 System Administration (Chapter 14),
>> there should be several SQL Server Performance Objects to track SQL
>> server activity, but...
>> I can't find them. What happend, where are they.
>> Anyone?
>> Thanks,
>> Bart
>|||Some Extra information (Had a chat with our System Administrator).
The W2003 Server runs 2 SQL servers. 1 SQL Server 2000 and another that came
with our backup program (BACKUPEXEC) to store backup information. The last
one I can see in the Performance Monitor. But that's not the one I would
like to monitor. Any idea how to get the objects of the other SQL server
into the performance monitor, or maybe how to swap them.
thanks
Bart|||Bart
http://support.microsoft.com/kb/227662
http://support.microsoft.com/kb/246328
"Bart Steur" <solnews@.xs4all.nl> wrote in message
news:uJzNb68PHHA.2312@.TK2MSFTNGP04.phx.gbl...
> Some Extra information (Had a chat with our System Administrator).
> The W2003 Server runs 2 SQL servers. 1 SQL Server 2000 and another that
> came with our backup program (BACKUPEXEC) to store backup information. The
> last one I can see in the Performance Monitor. But that's not the one I
> would like to monitor. Any idea how to get the objects of the other SQL
> server into the performance monitor, or maybe how to swap them.
> thanks
> Bart
>|||Those articles do not apply to SQLServer 2000.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eXynY$8PHHA.5012@.TK2MSFTNGP02.phx.gbl...
> Bart
> http://support.microsoft.com/kb/227662
> http://support.microsoft.com/kb/246328
> "Bart Steur" <solnews@.xs4all.nl> wrote in message
> news:uJzNb68PHHA.2312@.TK2MSFTNGP04.phx.gbl...
>> Some Extra information (Had a chat with our System Administrator).
>> The W2003 Server runs 2 SQL servers. 1 SQL Server 2000 and another that
>> came with our backup program (BACKUPEXEC) to store backup information.
>> The last one I can see in the Performance Monitor. But that's not the one
>> I would like to monitor. Any idea how to get the objects of the other SQL
>> server into the performance monitor, or maybe how to swap them.
>> thanks
>> Bart
>|||Bart
You are right, my mistake, sorry
For detailed information, please reference the following article:
330088 BUG: SQLServer:Databases Performance Counters Limited to First 99
http://support.microsoft.com/?id=330088
SQL Server 2000 Performance Counters Do Not Appear in the Performance
http://support.microsoft.com/?id=827260
http://support.microsoft.com/?id=812915
"Bart Steur" <solnews@.xs4all.nl> wrote in message
news:u5fIPIGQHHA.2468@.TK2MSFTNGP06.phx.gbl...
> Those articles do not apply to SQLServer 2000.
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eXynY$8PHHA.5012@.TK2MSFTNGP02.phx.gbl...
>> Bart
>> http://support.microsoft.com/kb/227662
>> http://support.microsoft.com/kb/246328
>> "Bart Steur" <solnews@.xs4all.nl> wrote in message
>> news:uJzNb68PHHA.2312@.TK2MSFTNGP04.phx.gbl...
>> Some Extra information (Had a chat with our System Administrator).
>> The W2003 Server runs 2 SQL servers. 1 SQL Server 2000 and another that
>> came with our backup program (BACKUPEXEC) to store backup information.
>> The last one I can see in the Performance Monitor. But that's not the
>> one I would like to monitor. Any idea how to get the objects of the
>> other SQL server into the performance monitor, or maybe how to swap
>> them.
>> thanks
>> Bart
>>
>|||Thanks Uri, but none of the provided solutions helped.
Maby it is because the SQL Server used by our Backup Program (BACKUPEXEC) is
a DESKTOP version.
The 'real' SQL Server used for our data is an ENTERPRISE edition.
FYI. I'm not interested in the performance of the DESKTOP version. So those
may be eliminated from the Perfmon.
By the way, the reason I'm so eager to see the performance, is because we
plugged in an additional 2GB (making it 4GB) and I can't see any difference
in (heavy) query speeds. So I lookup the tuning part in the SQLS2000 System
Administration book and that talked about the perfmon.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:udOdfOGQHHA.4172@.TK2MSFTNGP04.phx.gbl...
> Bart
> You are right, my mistake, sorry
> For detailed information, please reference the following article:
> 330088 BUG: SQLServer:Databases Performance Counters Limited to First 99
> http://support.microsoft.com/?id=330088
>
> SQL Server 2000 Performance Counters Do Not Appear in the Performance
> http://support.microsoft.com/?id=827260
> http://support.microsoft.com/?id=812915
>
> "Bart Steur" <solnews@.xs4all.nl> wrote in message
> news:u5fIPIGQHHA.2468@.TK2MSFTNGP06.phx.gbl...
>> Those articles do not apply to SQLServer 2000.
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:eXynY$8PHHA.5012@.TK2MSFTNGP02.phx.gbl...
>> Bart
>> http://support.microsoft.com/kb/227662
>> http://support.microsoft.com/kb/246328
>> "Bart Steur" <solnews@.xs4all.nl> wrote in message
>> news:uJzNb68PHHA.2312@.TK2MSFTNGP04.phx.gbl...
>> Some Extra information (Had a chat with our System Administrator).
>> The W2003 Server runs 2 SQL servers. 1 SQL Server 2000 and another that
>> came with our backup program (BACKUPEXEC) to store backup information.
>> The last one I can see in the Performance Monitor. But that's not the
>> one I would like to monitor. Any idea how to get the objects of the
>> other SQL server into the performance monitor, or maybe how to swap
>> them.
>> thanks
>> Bart
>>
>>
>|||Bart
> By the way, the reason I'm so eager to see the performance, is because we
> plugged in an additional 2GB (making it 4GB) and I can't see any
> difference in (heavy) query speeds. So I lookup the tuning part in the
> SQLS2000 System Administration book and that talked about the perfmon.
I have my doubt that a 'bad' written query will perform better if you add
more memory. I'd run SQL Server Profiler to identify long running queries
and start tunning them
"Bart Steur" <solnews@.xs4all.nl> wrote in message
news:uWovibGQHHA.3624@.TK2MSFTNGP05.phx.gbl...
> Thanks Uri, but none of the provided solutions helped.
> Maby it is because the SQL Server used by our Backup Program (BACKUPEXEC)
> is a DESKTOP version.
> The 'real' SQL Server used for our data is an ENTERPRISE edition.
> FYI. I'm not interested in the performance of the DESKTOP version. So
> those may be eliminated from the Perfmon.
> By the way, the reason I'm so eager to see the performance, is because we
> plugged in an additional 2GB (making it 4GB) and I can't see any
> difference in (heavy) query speeds. So I lookup the tuning part in the
> SQLS2000 System Administration book and that talked about the perfmon.
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:udOdfOGQHHA.4172@.TK2MSFTNGP04.phx.gbl...
>> Bart
>> You are right, my mistake, sorry
>> For detailed information, please reference the following article:
>> 330088 BUG: SQLServer:Databases Performance Counters Limited to First 99
>> http://support.microsoft.com/?id=330088
>>
>> SQL Server 2000 Performance Counters Do Not Appear in the Performance
>> http://support.microsoft.com/?id=827260
>> http://support.microsoft.com/?id=812915
>>
>> "Bart Steur" <solnews@.xs4all.nl> wrote in message
>> news:u5fIPIGQHHA.2468@.TK2MSFTNGP06.phx.gbl...
>> Those articles do not apply to SQLServer 2000.
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:eXynY$8PHHA.5012@.TK2MSFTNGP02.phx.gbl...
>> Bart
>> http://support.microsoft.com/kb/227662
>> http://support.microsoft.com/kb/246328
>> "Bart Steur" <solnews@.xs4all.nl> wrote in message
>> news:uJzNb68PHHA.2312@.TK2MSFTNGP04.phx.gbl...
>> Some Extra information (Had a chat with our System Administrator).
>> The W2003 Server runs 2 SQL servers. 1 SQL Server 2000 and another
>> that came with our backup program (BACKUPEXEC) to store backup
>> information. The last one I can see in the Performance Monitor. But
>> that's not the one I would like to monitor. Any idea how to get the
>> objects of the other SQL server into the performance monitor, or maybe
>> how to swap them.
>> thanks
>> Bart
>>
>>
>>
>|||Hello Bart,
Can you pl. check the AWE option?
http://msdn2.microsoft.com/en-us/library/aa213764(SQL.80).aspx
Cheers,
MB
"Bart Steur" <solnews@.xs4all.nl> wrote in message
news:uWovibGQHHA.3624@.TK2MSFTNGP05.phx.gbl...
> Thanks Uri, but none of the provided solutions helped.
> Maby it is because the SQL Server used by our Backup Program (BACKUPEXEC)
> is a DESKTOP version.
> The 'real' SQL Server used for our data is an ENTERPRISE edition.
> FYI. I'm not interested in the performance of the DESKTOP version. So
> those may be eliminated from the Perfmon.
> By the way, the reason I'm so eager to see the performance, is because we
> plugged in an additional 2GB (making it 4GB) and I can't see any
> difference in (heavy) query speeds. So I lookup the tuning part in the
> SQLS2000 System Administration book and that talked about the perfmon.
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:udOdfOGQHHA.4172@.TK2MSFTNGP04.phx.gbl...
>> Bart
>> You are right, my mistake, sorry
>> For detailed information, please reference the following article:
>> 330088 BUG: SQLServer:Databases Performance Counters Limited to First 99
>> http://support.microsoft.com/?id=330088
>>
>> SQL Server 2000 Performance Counters Do Not Appear in the Performance
>> http://support.microsoft.com/?id=827260
>> http://support.microsoft.com/?id=812915
>>
>> "Bart Steur" <solnews@.xs4all.nl> wrote in message
>> news:u5fIPIGQHHA.2468@.TK2MSFTNGP06.phx.gbl...
>> Those articles do not apply to SQLServer 2000.
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:eXynY$8PHHA.5012@.TK2MSFTNGP02.phx.gbl...
>> Bart
>> http://support.microsoft.com/kb/227662
>> http://support.microsoft.com/kb/246328
>> "Bart Steur" <solnews@.xs4all.nl> wrote in message
>> news:uJzNb68PHHA.2312@.TK2MSFTNGP04.phx.gbl...
>> Some Extra information (Had a chat with our System Administrator).
>> The W2003 Server runs 2 SQL servers. 1 SQL Server 2000 and another
>> that came with our backup program (BACKUPEXEC) to store backup
>> information. The last one I can see in the Performance Monitor. But
>> that's not the one I would like to monitor. Any idea how to get the
>> objects of the other SQL server into the performance monitor, or maybe
>> how to swap them.
>> thanks
>> Bart
>>
>>
>>
>|||That would be the next step, but I got stuck at perfmon, because I couldn't
find any counters.
I'll skip to that part now, and hope to find info on the counters in the
mean time. If you have any more info, please let me know.
regards,
Bart
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23jD6leGQHHA.780@.TK2MSFTNGP03.phx.gbl...
> Bart
>> By the way, the reason I'm so eager to see the performance, is because we
>> plugged in an additional 2GB (making it 4GB) and I can't see any
>> difference in (heavy) query speeds. So I lookup the tuning part in the
>> SQLS2000 System Administration book and that talked about the perfmon.
> I have my doubt that a 'bad' written query will perform better if you add
> more memory. I'd run SQL Server Profiler to identify long running queries
> and start tunning them
>
>
>
> "Bart Steur" <solnews@.xs4all.nl> wrote in message
> news:uWovibGQHHA.3624@.TK2MSFTNGP05.phx.gbl...
>> Thanks Uri, but none of the provided solutions helped.
>> Maby it is because the SQL Server used by our Backup Program (BACKUPEXEC)
>> is a DESKTOP version.
>> The 'real' SQL Server used for our data is an ENTERPRISE edition.
>> FYI. I'm not interested in the performance of the DESKTOP version. So
>> those may be eliminated from the Perfmon.
>> By the way, the reason I'm so eager to see the performance, is because we
>> plugged in an additional 2GB (making it 4GB) and I can't see any
>> difference in (heavy) query speeds. So I lookup the tuning part in the
>> SQLS2000 System Administration book and that talked about the perfmon.
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:udOdfOGQHHA.4172@.TK2MSFTNGP04.phx.gbl...
>> Bart
>> You are right, my mistake, sorry
>> For detailed information, please reference the following article:
>> 330088 BUG: SQLServer:Databases Performance Counters Limited to First 99
>> http://support.microsoft.com/?id=330088
>>
>> SQL Server 2000 Performance Counters Do Not Appear in the Performance
>> http://support.microsoft.com/?id=827260
>> http://support.microsoft.com/?id=812915
>>
>> "Bart Steur" <solnews@.xs4all.nl> wrote in message
>> news:u5fIPIGQHHA.2468@.TK2MSFTNGP06.phx.gbl...
>> Those articles do not apply to SQLServer 2000.
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:eXynY$8PHHA.5012@.TK2MSFTNGP02.phx.gbl...
>> Bart
>> http://support.microsoft.com/kb/227662
>> http://support.microsoft.com/kb/246328
>> "Bart Steur" <solnews@.xs4all.nl> wrote in message
>> news:uJzNb68PHHA.2312@.TK2MSFTNGP04.phx.gbl...
>> Some Extra information (Had a chat with our System Administrator).
>> The W2003 Server runs 2 SQL servers. 1 SQL Server 2000 and another
>> that came with our backup program (BACKUPEXEC) to store backup
>> information. The last one I can see in the Performance Monitor. But
>> that's not the one I would like to monitor. Any idea how to get the
>> objects of the other SQL server into the performance monitor, or
>> maybe how to swap them.
>> thanks
>> Bart
>>
>>
>>
>>
>|||I've looked into this, but I don't think this option applies to my
situation. First we are using Window 2003 Server Standard, and second this
is for very large memory sizes. We only have 4 GB. But thanks for the
support. Any other suggestions are welcome.
Regards
Bart
"MB" <MB@.MB.com> wrote in message
news:uedn1jGQHHA.4172@.TK2MSFTNGP04.phx.gbl...
> Hello Bart,
> Can you pl. check the AWE option?
> http://msdn2.microsoft.com/en-us/library/aa213764(SQL.80).aspx
> Cheers,
> MB
> "Bart Steur" <solnews@.xs4all.nl> wrote in message
> news:uWovibGQHHA.3624@.TK2MSFTNGP05.phx.gbl...
>> Thanks Uri, but none of the provided solutions helped.
>> Maby it is because the SQL Server used by our Backup Program (BACKUPEXEC)
>> is a DESKTOP version.
>> The 'real' SQL Server used for our data is an ENTERPRISE edition.
>> FYI. I'm not interested in the performance of the DESKTOP version. So
>> those may be eliminated from the Perfmon.
>> By the way, the reason I'm so eager to see the performance, is because we
>> plugged in an additional 2GB (making it 4GB) and I can't see any
>> difference in (heavy) query speeds. So I lookup the tuning part in the
>> SQLS2000 System Administration book and that talked about the perfmon.
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:udOdfOGQHHA.4172@.TK2MSFTNGP04.phx.gbl...
>> Bart
>> You are right, my mistake, sorry
>> For detailed information, please reference the following article:
>> 330088 BUG: SQLServer:Databases Performance Counters Limited to First 99
>> http://support.microsoft.com/?id=330088
>>
>> SQL Server 2000 Performance Counters Do Not Appear in the Performance
>> http://support.microsoft.com/?id=827260
>> http://support.microsoft.com/?id=812915
>>
>> "Bart Steur" <solnews@.xs4all.nl> wrote in message
>> news:u5fIPIGQHHA.2468@.TK2MSFTNGP06.phx.gbl...
>> Those articles do not apply to SQLServer 2000.
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:eXynY$8PHHA.5012@.TK2MSFTNGP02.phx.gbl...
>> Bart
>> http://support.microsoft.com/kb/227662
>> http://support.microsoft.com/kb/246328
>> "Bart Steur" <solnews@.xs4all.nl> wrote in message
>> news:uJzNb68PHHA.2312@.TK2MSFTNGP04.phx.gbl...
>> Some Extra information (Had a chat with our System Administrator).
>> The W2003 Server runs 2 SQL servers. 1 SQL Server 2000 and another
>> that came with our backup program (BACKUPEXEC) to store backup
>> information. The last one I can see in the Performance Monitor. But
>> that's not the one I would like to monitor. Any idea how to get the
>> objects of the other SQL server into the performance monitor, or
>> maybe how to swap them.
>> thanks
>> Bart
>>
>>
>>
>>
>|||Try here: http://www.extremeexperts.com/SQL/FAQ/EnablingPerCounters.aspx
John
Bart Steur wrote:
> I've looked into this, but I don't think this option applies to my
> situation. First we are using Window 2003 Server Standard, and second this
> is for very large memory sizes. We only have 4 GB. But thanks for the
> support. Any other suggestions are welcome.
> Regards
> Bart
> "MB" <MB@.MB.com> wrote in message
> news:uedn1jGQHHA.4172@.TK2MSFTNGP04.phx.gbl...
>>Hello Bart,
>>Can you pl. check the AWE option?
>>http://msdn2.microsoft.com/en-us/library/aa213764(SQL.80).aspx
>>Cheers,
>>MB
>>"Bart Steur" <solnews@.xs4all.nl> wrote in message
>>news:uWovibGQHHA.3624@.TK2MSFTNGP05.phx.gbl...
>>Thanks Uri, but none of the provided solutions helped.
>>Maby it is because the SQL Server used by our Backup Program (BACKUPEXEC)
>>is a DESKTOP version.
>>The 'real' SQL Server used for our data is an ENTERPRISE edition.
>>FYI. I'm not interested in the performance of the DESKTOP version. So
>>those may be eliminated from the Perfmon.
>>By the way, the reason I'm so eager to see the performance, is because we
>>plugged in an additional 2GB (making it 4GB) and I can't see any
>>difference in (heavy) query speeds. So I lookup the tuning part in the
>>SQLS2000 System Administration book and that talked about the perfmon.
>>
>>"Uri Dimant" <urid@.iscar.co.il> wrote in message
>>news:udOdfOGQHHA.4172@.TK2MSFTNGP04.phx.gbl...
>>Bart
>>You are right, my mistake, sorry
>>For detailed information, please reference the following article:
>>330088 BUG: SQLServer:Databases Performance Counters Limited to First 99
>>http://support.microsoft.com/?id=330088
>>
>>SQL Server 2000 Performance Counters Do Not Appear in the Performance
>>http://support.microsoft.com/?id=827260
>>http://support.microsoft.com/?id=812915
>>
>>"Bart Steur" <solnews@.xs4all.nl> wrote in message
>>news:u5fIPIGQHHA.2468@.TK2MSFTNGP06.phx.gbl...
>>Those articles do not apply to SQLServer 2000.
>>
>>"Uri Dimant" <urid@.iscar.co.il> wrote in message
>>news:eXynY$8PHHA.5012@.TK2MSFTNGP02.phx.gbl...
>>Bart
>>http://support.microsoft.com/kb/227662
>>http://support.microsoft.com/kb/246328
>>"Bart Steur" <solnews@.xs4all.nl> wrote in message
>>news:uJzNb68PHHA.2312@.TK2MSFTNGP04.phx.gbl...
>>>Some Extra information (Had a chat with our System Administrator).
>>>
>>>The W2003 Server runs 2 SQL servers. 1 SQL Server 2000 and another
>>>that came with our backup program (BACKUPEXEC) to store backup
>>>information. The last one I can see in the Performance Monitor. But
>>>that's not the one I would like to monitor. Any idea how to get the
>>>objects of the other SQL server into the performance monitor, or
>>>maybe how to swap them.
>>>
>>>thanks
>>>Bart
>>>
>>
>>
>>
>|||Works like a charm. Thanks John John
"John John" <audetweld@.nbnet.nb.ca> wrote in message
news:OdafzcHQHHA.1380@.TK2MSFTNGP05.phx.gbl...
> Try here: http://www.extremeexperts.com/SQL/FAQ/EnablingPerCounters.aspx
> John
> Bart Steur wrote:
>> I've looked into this, but I don't think this option applies to my
>> situation. First we are using Window 2003 Server Standard, and second
>> this is for very large memory sizes. We only have 4 GB. But thanks for
>> the support. Any other suggestions are welcome.
>> Regards
>> Bart
>> "MB" <MB@.MB.com> wrote in message
>> news:uedn1jGQHHA.4172@.TK2MSFTNGP04.phx.gbl...
>>Hello Bart,
>>Can you pl. check the AWE option?
>>http://msdn2.microsoft.com/en-us/library/aa213764(SQL.80).aspx
>>Cheers,
>>MB
>>"Bart Steur" <solnews@.xs4all.nl> wrote in message
>>news:uWovibGQHHA.3624@.TK2MSFTNGP05.phx.gbl...
>>Thanks Uri, but none of the provided solutions helped.
>>Maby it is because the SQL Server used by our Backup Program
>>(BACKUPEXEC) is a DESKTOP version.
>>The 'real' SQL Server used for our data is an ENTERPRISE edition.
>>FYI. I'm not interested in the performance of the DESKTOP version. So
>>those may be eliminated from the Perfmon.
>>By the way, the reason I'm so eager to see the performance, is because
>>we plugged in an additional 2GB (making it 4GB) and I can't see any
>>difference in (heavy) query speeds. So I lookup the tuning part in the
>>SQLS2000 System Administration book and that talked about the perfmon.
>>
>>"Uri Dimant" <urid@.iscar.co.il> wrote in message
>>news:udOdfOGQHHA.4172@.TK2MSFTNGP04.phx.gbl...
>>Bart
>>You are right, my mistake, sorry
>>For detailed information, please reference the following article:
>>330088 BUG: SQLServer:Databases Performance Counters Limited to First
>>99
>>http://support.microsoft.com/?id=330088
>>
>>SQL Server 2000 Performance Counters Do Not Appear in the Performance
>>http://support.microsoft.com/?id=827260
>>http://support.microsoft.com/?id=812915
>>
>>"Bart Steur" <solnews@.xs4all.nl> wrote in message
>>news:u5fIPIGQHHA.2468@.TK2MSFTNGP06.phx.gbl...
>>Those articles do not apply to SQLServer 2000.
>>
>>"Uri Dimant" <urid@.iscar.co.il> wrote in message
>>news:eXynY$8PHHA.5012@.TK2MSFTNGP02.phx.gbl...
>>>Bart
>>>http://support.microsoft.com/kb/227662
>>>http://support.microsoft.com/kb/246328
>>>
>>>"Bart Steur" <solnews@.xs4all.nl> wrote in message
>>>news:uJzNb68PHHA.2312@.TK2MSFTNGP04.phx.gbl...
>>>
>>>Some Extra information (Had a chat with our System Administrator).
>>>
>>>The W2003 Server runs 2 SQL servers. 1 SQL Server 2000 and another
>>>that came with our backup program (BACKUPEXEC) to store backup
>>>information. The last one I can see in the Performance Monitor. But
>>>that's not the one I would like to monitor. Any idea how to get the
>>>objects of the other SQL server into the performance monitor, or
>>>maybe how to swap them.
>>>
>>>thanks
>>>Bart
>>>
>>>
>>>
>>
>>|||You're welcome.
John
Bart Steur wrote:
> Works like a charm. Thanks John John
> "John John" <audetweld@.nbnet.nb.ca> wrote in message
> news:OdafzcHQHHA.1380@.TK2MSFTNGP05.phx.gbl...
>>Try here: http://www.extremeexperts.com/SQL/FAQ/EnablingPerCounters.aspx
>>John
>>Bart Steur wrote:
>>
>>I've looked into this, but I don't think this option applies to my
>>situation. First we are using Window 2003 Server Standard, and second
>>this is for very large memory sizes. We only have 4 GB. But thanks for
>>the support. Any other suggestions are welcome.
>>Regards
>>Bart
>>"MB" <MB@.MB.com> wrote in message
>>news:uedn1jGQHHA.4172@.TK2MSFTNGP04.phx.gbl...
>>
>>Hello Bart,
>>Can you pl. check the AWE option?
>>http://msdn2.microsoft.com/en-us/library/aa213764(SQL.80).aspx
>>Cheers,
>>MB
>>"Bart Steur" <solnews@.xs4all.nl> wrote in message
>>news:uWovibGQHHA.3624@.TK2MSFTNGP05.phx.gbl...
>>
>>Thanks Uri, but none of the provided solutions helped.
>>Maby it is because the SQL Server used by our Backup Program
>>(BACKUPEXEC) is a DESKTOP version.
>>The 'real' SQL Server used for our data is an ENTERPRISE edition.
>>FYI. I'm not interested in the performance of the DESKTOP version. So
>>those may be eliminated from the Perfmon.
>>By the way, the reason I'm so eager to see the performance, is because
>>we plugged in an additional 2GB (making it 4GB) and I can't see any
>>difference in (heavy) query speeds. So I lookup the tuning part in the
>>SQLS2000 System Administration book and that talked about the perfmon.
>>
>>"Uri Dimant" <urid@.iscar.co.il> wrote in message
>>news:udOdfOGQHHA.4172@.TK2MSFTNGP04.phx.gbl...
>>
>>Bart
>>You are right, my mistake, sorry
>>For detailed information, please reference the following article:
>>330088 BUG: SQLServer:Databases Performance Counters Limited to First
>>99
>>http://support.microsoft.com/?id=330088
>>
>>SQL Server 2000 Performance Counters Do Not Appear in the Performance
>>http://support.microsoft.com/?id=827260
>>http://support.microsoft.com/?id=812915
>>
>>"Bart Steur" <solnews@.xs4all.nl> wrote in message
>>news:u5fIPIGQHHA.2468@.TK2MSFTNGP06.phx.gbl...
>>
>>>Those articles do not apply to SQLServer 2000.
>>>
>>>
>>>
>>>"Uri Dimant" <urid@.iscar.co.il> wrote in message
>>>news:eXynY$8PHHA.5012@.TK2MSFTNGP02.phx.gbl...
>>>
>>>
>>>Bart
>>>http://support.microsoft.com/kb/227662
>>>http://support.microsoft.com/kb/246328
>>>
>>>"Bart Steur" <solnews@.xs4all.nl> wrote in message
>>>news:uJzNb68PHHA.2312@.TK2MSFTNGP04.phx.gbl...
>>>
>>>
>>>Some Extra information (Had a chat with our System Administrator).
>>>
>>>The W2003 Server runs 2 SQL servers. 1 SQL Server 2000 and another
>>>that came with our backup program (BACKUPEXEC) to store backup
>>>information. The last one I can see in the Performance Monitor. But
>>>that's not the one I would like to monitor. Any idea how to get the
>>>objects of the other SQL server into the performance monitor, or
>>>maybe how to swap them.
>>>
>>>thanks
>>>Bart
>>>
>>>
>>>
>

Performance object list is missing

Hi, After i installed SP3 the list of Performance Object of the Performance console is missing and the logs of SQL show "Performance monitor shared memory setup failed: -1".
Best Regards
Manuel
FIX: "Performance Monitor Shared Memory Setup Failed: -1" Error Message When
You Start SQL Server
http://support.microsoft.com/default...812915&sd=tech
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Manuel" <Manuel@.discussions.microsoft.com> wrote in message
news:81930B3B-A7FB-4118-A6F6-FA3E15B6154A@.microsoft.com...
> Hi, After i installed SP3 the list of Performance Object of the
Performance console is missing and the logs of SQL show "Performance monitor
shared memory setup failed: -1".
> --
> Best Regards
> Manuel
|||Hi, i forgot to tell that i have a virtual instance of SQL and the fix you tell me doesn't work for this issue.
Thanks
Manuel
Best Regards
Manuel
"Jasper Smith" wrote:

> FIX: "Performance Monitor Shared Memory Setup Failed: -1" Error Message When
> You Start SQL Server
> http://support.microsoft.com/default...812915&sd=tech
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Manuel" <Manuel@.discussions.microsoft.com> wrote in message
> news:81930B3B-A7FB-4118-A6F6-FA3E15B6154A@.microsoft.com...
> Performance console is missing and the logs of SQL show "Performance monitor
> shared memory setup failed: -1".
>
>

Performance object list is missing

Hi, After i installed SP3 the list of Performance Object of the Performance
console is missing and the logs of SQL show "Performance monitor shared memo
ry setup failed: -1".
--
Best Regards
ManuelFIX: "Performance Monitor Shared Memory Setup Failed: -1" Error Message When
You Start SQL Server
http://support.microsoft.com/defaul...;812915&sd=tech
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Manuel" <Manuel@.discussions.microsoft.com> wrote in message
news:81930B3B-A7FB-4118-A6F6-FA3E15B6154A@.microsoft.com...
> Hi, After i installed SP3 the list of Performance Object of the
Performance console is missing and the logs of SQL show "Performance monitor
shared memory setup failed: -1".
> --
> Best Regards
> Manuel|||Hi, i forgot to tell that i have a virtual instance of SQL and the fix you t
ell me doesn't work for this issue.
Thanks
Manuel
Best Regards
Manuel
"Jasper Smith" wrote:

> FIX: "Performance Monitor Shared Memory Setup Failed: -1" Error Message Wh
en
> You Start SQL Server
> http://support.microsoft.com/defaul...;812915&sd=tech
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Manuel" <Manuel@.discussions.microsoft.com> wrote in message
> news:81930B3B-A7FB-4118-A6F6-FA3E15B6154A@.microsoft.com...
> Performance console is missing and the logs of SQL show "Performance monit
or
> shared memory setup failed: -1".
>
>

Performance object "SQL Server" does not exist

Hi,

I am running SQL Server 2000 SP4 on a Windows Server 2003 64bit Edition. I indend to monitor the SQL Server performance in the Windows Performance Monitor however neither the SQL Server performance object nor the associated counters are visible.

Any idea why and how I possibly install it?

Thanks in advance,

Greg

When ever I've had the counters drop off either a SQL Server restart or an OS restart will resolve the issue.|||

Thanks.

I did so however the counters haven't emerged after the restart of the both.

Any help would be appreciated.

Greg

|||

Come on........! The suggestion to restart the servers shows little respect towards this issue.

Would somebody please help?

Thanks.

|||

Is the SQL server a 64 bit edition?

THe issue is you can only see 64bit related counters and only way you load the 32bit version of PERFMON to be able to read the 32 bit SQL Server counters, that is easily done by running this: mmc /32 perfmon.msc.

|||

mmc /32 perfmon.msc was the solution.

Thanks!

Performance mystery -- SP vs script

I have encountred situations like this before, but this one
has me stumped.

I have a pretty simple SP that collects information about
residential properties from a large database. First step is to
query on the basis of address or location, and collect a temp
table of property IDs. Second step is to populate a composite
table of property information by joining the IDs to a table of
characterisitics. Third step is to update some fields by finding
a single value from multiple candidates in large tables -- one
has 275 million, another 325 million rows -- e.g., the price of
the most recent sale for a property.

As an SP, this takes absolutely forever, and it seems it's doing
endless scans of the large tables. So to analyze it, I took the
code and ran it as a script -- turned the parameter definition at
the top into a DECLARE statement, set values for the variables
that are the input parameters, no other changes, and go. Presto!
It runs in no time flat, and the query plans reveal it's using
the indexes just like it's supposed to. But the SP might take
an hour to do the same thing.

Any suggestions about what to look for? I believe both versions
have fresh query plans -- I have recompiled (and dropped and
recreated) the SP, and the plain script should have a fresh plan.
Maybe it's because the parameter values are known when the script
runs, but not when the SP is complied? I would really appreciate
any pointers, and can provide more information as needed.

Thanks,
Jim GeissmanPS -- it's the update statements that are the problem.
Even if the temp table has only one record, four parallel
threads are spawned to search through a large table
hunting for the ten or so records for that property -- even
though thereare indexes aplenty allowing it to go directly
to the appropriate records.|||You might want to try recompiling the stored procedure. It's possible
that when the stored procedure was first run and a query plan was
created, there were different indexes or the tables did not hold the
same data. The stored procedure might still be trying to use that same
query plan even though the data and/or indexes have changed.

HTH,
-Tom.|||That sure sounds reasonable, Tom, however nothing has changed
with the tables for a week or more, and the SP has only existed
for two days. One change I did make that seems to have led to
this was:

I had approximately this formerly:

update t set amt=e.amt, date=e.date,name=n.name
from #temp t join event e on e.propid=t.propid
join name n on n.propid=e.propid and n.eventid=e.eventid
where e.eventtype=... and e.date=(select max(date)
from event e1 where e1.propid=t.propid and...)

I split it into two separate update queries, one for the
date & amount, another for the name, because the two
should each be the most recent (with some conditions),
but don't have to be from the same event. The joins
dropped from three tables to two. That change broke it.|||There's an index that has everything needed to locate the
amount -- propid, date, eventtype, and amount, in that order.
However, it is being scanned on the basis of date and event
type (millions of rows) instead of going directly to the
correct handful of rows by adding propid. Is there a way to
change this behavior?|||On 9 Mar 2005 15:38:30 -0800, jim_geissman@.countrywide.com wrote:

>PS -- it's the update statements that are the problem.
>Even if the temp table has only one record, four parallel
>threads are spawned to search through a large table
>hunting for the ten or so records for that property -- even
>though thereare indexes aplenty allowing it to go directly
>to the appropriate records.

Which version of SQL Server are you using? I've had problems with SQL Server
7 grossly misestimating the time to execute query plans, thinking it has
exceeded the parallelism threshold, and choosing to use parallelism when it
actually hurts performance significantly. Try changing your server's
parallelism threshold to 5x or 10x the default value.|||(jim_geissman@.countrywide.com) writes:
> I have encountred situations like this before, but this one
> has me stumped.
> I have a pretty simple SP that collects information about
> residential properties from a large database. First step is to
> query on the basis of address or location, and collect a temp
> table of property IDs. Second step is to populate a composite
> table of property information by joining the IDs to a table of
> characterisitics. Third step is to update some fields by finding
> a single value from multiple candidates in large tables -- one
> has 275 million, another 325 million rows -- e.g., the price of
> the most recent sale for a property.
> As an SP, this takes absolutely forever, and it seems it's doing
> endless scans of the large tables. So to analyze it, I took the
> code and ran it as a script -- turned the parameter definition at
> the top into a DECLARE statement, set values for the variables
> that are the input parameters, no other changes, and go. Presto!
> It runs in no time flat, and the query plans reveal it's using
> the indexes just like it's supposed to. But the SP might take
> an hour to do the same thing.

Without seeing the code, and not having information about the table etc,
the best I can offer is wild guesses and standard recommendations.

Since replacing the parameters with variables appeared to give effect,
it seems you have a workaround. (Copy the parameters to local variables.)
Then again, there are a couple of more possibilities.

If the issue is indeed parameters vs. variables, then it could be a
case of psrameter sniffing when you don't want it. "Parameter sniffing"
is when SQL Server uses the values of the input parameters on the
first invocation to build the query plan for the stored procedure.
When it comes to variables, SQL Server does not know their values
before and builds the plan from standard assumptions. Usually parameter
sniffing is good, but say that you have:

CREATE PROCEDURE sniff_sp @.a datetime ... AS
...
IF @.a IS NULL SELECT @.a = convert(char(8), @.a, 112)

And on first invocation, you call the procedure with @.a = NULL. That's
a value you don't use, so it will not be good for the plan.

Another alternative is that the stored procedure was created with
any of ANSI_NULLS or QUOTED_IDENTIFIER off, and the procedure involves
access to an indexed view or an indexed computed columns. The two
mentioned settings must be ON for such indexes to be used, and these
two settings are saved with the stored procedure. You can use
the Objectproperty() function to investigate this.

There may be other reasons as well, as the optimizer being to optimistic
about the benefits of parallelism, or too pessimistic about the usefulness
of a non-clustered index. You can use OPTION (MAXDOP 1) to turn of
parallelism. You can also try to force the use of a certain index, but
you should be careful, because a using a non-clustered index when
there are too many hits and be real disaster performancewise.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Performance Monitors with SAN

We have recently moved our database to a SAN and I am trying to monitor performance. Does anyone know of a good way to monitor performance going to the SAN? I have been watching things like %Disk time and Avg Disk Queue Length, but does anyone know if these are acurrate when referring to a SAN? My %Disk Time is going up to numbers as high as 1200. The Disk queue length seems kind of low for seeing that high disk time as well.

Any help would be great.

Thanks much.I a junior DBA here and the senior dba in charge of our sql cluster monitors disk write bytes/sec, disk read bytes/sec, and average disk queue length among others things and I'm pretty sure our stuff's on a SAN. He says anything approaching 1 or above for queue length is cause for alarm. I think read & write throughput are specific to the hardware.

Wish I could give you a better answer...|||So what the performance monitor is telling you is that your disk is writing twelve times as fast as it can (not real likely using conventional hardware, but it happens frequently using a SAN). That would tend to lead to really short disk queue lengths.

I'd suggest that you talk to your SAN vendor. They should be able to give you some really good ideas on what you can monitor, especially within the SAN drivers themselves (if you are using SAN specific drivers).

You obviously have to adjust any counters delivered with the OS, since they are tuned for a very different world than a SAN. A good first guess is to scale the stock values by about a factor of 20, but you'll probably have to adjust that once you get a better feel for how your particular configuration performs.

-PatP|||We already did ask the SAN vender and they told us to purchase this software package they created to monitor performance. Personally I don't think that is necessary, so I was hoping I could monitor performance through these counters. I just don't know how they should be adjusted for the SAN.

Performance Monitors Start/Stop 7AM

I have Windows 2000 Advanced Server with SQL Server 2000
Enterprise with SP3A. I trying to set up a job to import
Performance Monitors daily into SQL Server table. How
would I set up Performance Monitor to stop at 7AM close
the present file, create a new file at 7:01AM start
collecting statistics.
This would be a very help to resolve this issue.
Thank You,
Dan J.There are some utilities that come with Xp and I believe are in the resource
kit of Win2000 that should help. One is called logman.exe and the other is
relog.exe. Logman can allow you to create, start, stop, change etc your
perfmon traces and relog will import them into a sql server table.
command_logman.mspx" target="_blank">http://www.microsoft.com/resources/...and_logman.mspx
command_relog.mspx" target="_blank">http://www.microsoft.com/resources/...mand_relog.mspx
Andrew J. Kelly SQL MVP
"Dan J." <anonymous@.discussions.microsoft.com> wrote in message
news:2311001c45e99$723e0190$a601280a@.phx
.gbl...
> I have Windows 2000 Advanced Server with SQL Server 2000
> Enterprise with SP3A. I trying to set up a job to import
> Performance Monitors daily into SQL Server table. How
> would I set up Performance Monitor to stop at 7AM close
> the present file, create a new file at 7:01AM start
> collecting statistics.
> This would be a very help to resolve this issue.
> Thank You,
> Dan J.
>

Performance Monitors Start/Stop 7AM

I have Windows 2000 Advanced Server with SQL Server 2000
Enterprise with SP3A. I trying to set up a job to import
Performance Monitors daily into SQL Server table. How
would I set up Performance Monitor to stop at 7AM close
the present file, create a new file at 7:01AM start
collecting statistics.
This would be a very help to resolve this issue.
Thank You,
Dan J.
There are some utilities that come with Xp and I believe are in the resource
kit of Win2000 that should help. One is called logman.exe and the other is
relog.exe. Logman can allow you to create, start, stop, change etc your
perfmon traces and relog will import them into a sql server table.
http://www.microsoft.com/resources/d...nd_logman.mspx
http://www.microsoft.com/resources/d...and_relog.mspx
Andrew J. Kelly SQL MVP
"Dan J." <anonymous@.discussions.microsoft.com> wrote in message
news:2311001c45e99$723e0190$a601280a@.phx.gbl...
> I have Windows 2000 Advanced Server with SQL Server 2000
> Enterprise with SP3A. I trying to set up a job to import
> Performance Monitors daily into SQL Server table. How
> would I set up Performance Monitor to stop at 7AM close
> the present file, create a new file at 7:01AM start
> collecting statistics.
> This would be a very help to resolve this issue.
> Thank You,
> Dan J.
>

Performance Monitors Start/Stop 7AM

I have Windows 2000 Advanced Server with SQL Server 2000
Enterprise with SP3A. I trying to set up a job to import
Performance Monitors daily into SQL Server table. How
would I set up Performance Monitor to stop at 7AM close
the present file, create a new file at 7:01AM start
collecting statistics.
This would be a very help to resolve this issue.
Thank You,
Dan J.There are some utilities that come with Xp and I believe are in the resource
kit of Win2000 that should help. One is called logman.exe and the other is
relog.exe. Logman can allow you to create, start, stop, change etc your
perfmon traces and relog will import them into a sql server table.
http://www.microsoft.com/resources/documentation/windows/xp/all/proddocs/en-us/nt_command_logman.mspx
http://www.microsoft.com/resources/documentation/windows/xp/all/proddocs/en-us/nt_command_relog.mspx
--
Andrew J. Kelly SQL MVP
"Dan J." <anonymous@.discussions.microsoft.com> wrote in message
news:2311001c45e99$723e0190$a601280a@.phx.gbl...
> I have Windows 2000 Advanced Server with SQL Server 2000
> Enterprise with SP3A. I trying to set up a job to import
> Performance Monitors daily into SQL Server table. How
> would I set up Performance Monitor to stop at 7AM close
> the present file, create a new file at 7:01AM start
> collecting statistics.
> This would be a very help to resolve this issue.
> Thank You,
> Dan J.
>

performance monitors on the SQL 2005

Looking to see if there is anyway to enable or capture or if already
available query performance stats on SQL2005
we are having issues with one of our databases.. and the programmers say
everything is ok.. ( they are outside verndors)
So is there anything we can get from SQL2005 to show us the times the
performance was low or high.. and such...
ASP, SQL2005, DW8 VBScript
Take a look at various wait stats counters in the Dynamic Management Views
exposed in SQL 2005. See BOL. Microsoft has a nice document on performance
analysis using waits and queues. Profiler can provide information on
execution times, cpu and i/o usage of executed queries.
Honestly though, the best way for you to prove if the issues are with the
application code or the database is to hire a pro for a quick performance
review.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Daniel" <dan_c@.h.com> wrote in message
news:OBHJIVrNIHA.2208@.TK2MSFTNGP06.phx.gbl...
> Looking to see if there is anyway to enable or capture or if already
> available query performance stats on SQL2005
> we are having issues with one of our databases.. and the programmers say
> everything is ok.. ( they are outside verndors)
> So is there anything we can get from SQL2005 to show us the times the
> performance was low or high.. and such...
> --
> ASP, SQL2005, DW8 VBScript
>
|||You can gleen this info from sys.dm_exec_query_stats with some limitations.
select total_worker_time/execution_count as AvgCPU
, total_elapsed_time/execution_count as AvgDuration
, (total_logical_reads+total_physical_reads)/execution_count as AvgReads
, execution_count
, substring(st.text, (qs.statement_start_offset/2)+1 , ((case
qs.statement_end_offset when -1 then datalength(st.text) else
qs.statement_end_offset end - qs.statement_start_offset)/2) + 1) as txt
, query_plan
from sys.dm_exec_query_stats as qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as st
cross apply sys.dm_exec_query_plan (qs.plan_handle) as qp
order by 2 desc
Jason Massie
www: http://statisticsio.com
rss: http://statisticsio.com/Home/tabid/36/rssid/1/Default.aspx
"Daniel" <dan_c@.h.com> wrote in message
news:OBHJIVrNIHA.2208@.TK2MSFTNGP06.phx.gbl...
> Looking to see if there is anyway to enable or capture or if already
> available query performance stats on SQL2005
> we are having issues with one of our databases.. and the programmers say
> everything is ok.. ( they are outside verndors)
> So is there anything we can get from SQL2005 to show us the times the
> performance was low or high.. and such...
> --
> ASP, SQL2005, DW8 VBScript
>
|||Daniel
http://www.sql-server-performance.com/articles/per/sys_dm_os_performance_counters_p1.aspx
"Daniel" <dan_c@.h.com> wrote in message
news:OBHJIVrNIHA.2208@.TK2MSFTNGP06.phx.gbl...
> Looking to see if there is anyway to enable or capture or if already
> available query performance stats on SQL2005
> we are having issues with one of our databases.. and the programmers say
> everything is ok.. ( they are outside verndors)
> So is there anything we can get from SQL2005 to show us the times the
> performance was low or high.. and such...
> --
> ASP, SQL2005, DW8 VBScript
>

performance monitors on the SQL 2005

Looking to see if there is anyway to enable or capture or if already
available query performance stats on SQL2005
we are having issues with one of our databases.. and the programmers say
everything is ok.. ( they are outside verndors)
So is there anything we can get from SQL2005 to show us the times the
performance was low or high.. and such...
--
ASP, SQL2005, DW8 VBScriptTake a look at various wait stats counters in the Dynamic Management Views
exposed in SQL 2005. See BOL. Microsoft has a nice document on performance
analysis using waits and queues. Profiler can provide information on
execution times, cpu and i/o usage of executed queries.
Honestly though, the best way for you to prove if the issues are with the
application code or the database is to hire a pro for a quick performance
review.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Daniel" <dan_c@.h.com> wrote in message
news:OBHJIVrNIHA.2208@.TK2MSFTNGP06.phx.gbl...
> Looking to see if there is anyway to enable or capture or if already
> available query performance stats on SQL2005
> we are having issues with one of our databases.. and the programmers say
> everything is ok.. ( they are outside verndors)
> So is there anything we can get from SQL2005 to show us the times the
> performance was low or high.. and such...
> --
> ASP, SQL2005, DW8 VBScript
>|||You can gleen this info from sys.dm_exec_query_stats with some limitations.
select total_worker_time/execution_count as AvgCPU
, total_elapsed_time/execution_count as AvgDuration
, (total_logical_reads+total_physical_reads)/execution_count as AvgReads
, execution_count
, substring(st.text, (qs.statement_start_offset/2)+1 , ((case
qs.statement_end_offset when -1 then datalength(st.text) else
qs.statement_end_offset end - qs.statement_start_offset)/2) + 1) as txt
, query_plan
from sys.dm_exec_query_stats as qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as st
cross apply sys.dm_exec_query_plan (qs.plan_handle) as qp
order by 2 desc
Jason Massie
www: http://statisticsio.com
rss: http://statisticsio.com/Home/tabid/36/rssid/1/Default.aspx
"Daniel" <dan_c@.h.com> wrote in message
news:OBHJIVrNIHA.2208@.TK2MSFTNGP06.phx.gbl...
> Looking to see if there is anyway to enable or capture or if already
> available query performance stats on SQL2005
> we are having issues with one of our databases.. and the programmers say
> everything is ok.. ( they are outside verndors)
> So is there anything we can get from SQL2005 to show us the times the
> performance was low or high.. and such...
> --
> ASP, SQL2005, DW8 VBScript
>|||Daniel
http://www.sql-server-performance.com/articles/per/sys_dm_os_performance_counters_p1.aspx
"Daniel" <dan_c@.h.com> wrote in message
news:OBHJIVrNIHA.2208@.TK2MSFTNGP06.phx.gbl...
> Looking to see if there is anyway to enable or capture or if already
> available query performance stats on SQL2005
> we are having issues with one of our databases.. and the programmers say
> everything is ok.. ( they are outside verndors)
> So is there anything we can get from SQL2005 to show us the times the
> performance was low or high.. and such...
> --
> ASP, SQL2005, DW8 VBScript
>

performance monitors on the SQL 2005

Looking to see if there is anyway to enable or capture or if already
available query performance stats on SQL2005
we are having issues with one of our databases.. and the programmers say
everything is ok.. ( they are outside verndors)
So is there anything we can get from SQL2005 to show us the times the
performance was low or high.. and such...
ASP, SQL2005, DW8 VBScriptTake a look at various wait stats counters in the Dynamic Management Views
exposed in SQL 2005. See BOL. Microsoft has a nice document on performance
analysis using waits and queues. Profiler can provide information on
execution times, cpu and i/o usage of executed queries.
Honestly though, the best way for you to prove if the issues are with the
application code or the database is to hire a pro for a quick performance
review.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Daniel" <dan_c@.h.com> wrote in message
news:OBHJIVrNIHA.2208@.TK2MSFTNGP06.phx.gbl...
> Looking to see if there is anyway to enable or capture or if already
> available query performance stats on SQL2005
> we are having issues with one of our databases.. and the programmers say
> everything is ok.. ( they are outside verndors)
> So is there anything we can get from SQL2005 to show us the times the
> performance was low or high.. and such...
> --
> ASP, SQL2005, DW8 VBScript
>|||You can gleen this info from sys.dm_exec_query_stats with some limitations.
select total_worker_time/execution_count as AvgCPU
, total_elapsed_time/execution_count as AvgDuration
, (total_logical_reads+total_physical_read
s)/execution_count as AvgReads
, execution_count
, substring(st.text, (qs.statement_start_offset/2)+1 , ((case
qs.statement_end_offset when -1 then datalength(st.text) else
qs.statement_end_offset end - qs.statement_start_offset)/2) + 1) as txt
, query_plan
from sys.dm_exec_query_stats as qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as st
cross apply sys.dm_exec_query_plan (qs.plan_handle) as qp
order by 2 desc
Jason Massie
www: http://statisticsio.com
rss: http://statisticsio.com/Home/tabid/.../1/Default.aspx
"Daniel" <dan_c@.h.com> wrote in message
news:OBHJIVrNIHA.2208@.TK2MSFTNGP06.phx.gbl...
> Looking to see if there is anyway to enable or capture or if already
> available query performance stats on SQL2005
> we are having issues with one of our databases.. and the programmers say
> everything is ok.. ( they are outside verndors)
> So is there anything we can get from SQL2005 to show us the times the
> performance was low or high.. and such...
> --
> ASP, SQL2005, DW8 VBScript
>|||Daniel
http://www.sql-server-performance.c...
p1.aspx
"Daniel" <dan_c@.h.com> wrote in message
news:OBHJIVrNIHA.2208@.TK2MSFTNGP06.phx.gbl...
> Looking to see if there is anyway to enable or capture or if already
> available query performance stats on SQL2005
> we are having issues with one of our databases.. and the programmers say
> everything is ok.. ( they are outside verndors)
> So is there anything we can get from SQL2005 to show us the times the
> performance was low or high.. and such...
> --
> ASP, SQL2005, DW8 VBScript
>

Performance Monitors on 64bit OS

I am using a 3rd party tool to bind to the perf monitors, with no success. I
n
the event log:
Windows cannot open the 32-bit extensible counter DLL MSSQLSERVER in a
64-bit environment.
Contact the file vendor to obtain a 64-bit version.
Alternatively, you can open the 32-bit extensible counter DLL by using the
32-bit version
of Performance Monitor. To use this tool, open the Windows folder, open the
Syswow64 folder,
and then start Perfmon.exe.
I followed and can see the monitors on the local machine using the 64bit
perfmon. (they do not show on the stat > run > perfmon
The question: Any ideas how to force the 64bit monitors to present
themselves rather then the 32bit - which do not have the sql perf monitirs.
Heeeelp.
ThanksOK - I have got myself mixed up.
I need the 32bit (Syswow64) perfmon to respond to the 3rd party software
rather than the 64bit.
Cheers
"Mike Duncan" wrote:

> I am using a 3rd party tool to bind to the perf monitors, with no success.
In
> the event log:
> Windows cannot open the 32-bit extensible counter DLL MSSQLSERVER in a
> 64-bit environment.
> Contact the file vendor to obtain a 64-bit version.
> Alternatively, you can open the 32-bit extensible counter DLL by using the
> 32-bit version
> of Performance Monitor. To use this tool, open the Windows folder, open th
e
> Syswow64 folder,
> and then start Perfmon.exe.
> I followed and can see the monitors on the local machine using the 64bit
> perfmon. (they do not show on the stat > run > perfmon
> The question: Any ideas how to force the 64bit monitors to present
> themselves rather then the 32bit - which do not have the sql perf monitirs
.
> Heeeelp.
> Thanks|||I have been searching the network and I have found nothing to answer
this question. I am having the same problem. Does anybody have an
idea?
ej
Mike Duncan wrote:
> I am using a 3rd party tool to bind to the perf monitors, with no success.
In
> the event log:
> Windows cannot open the 32-bit extensible counter DLL MSSQLSERVER in a
> 64-bit environment.
> Contact the file vendor to obtain a 64-bit version.
> Alternatively, you can open the 32-bit extensible counter DLL by using the
> 32-bit version
> of Performance Monitor. To use this tool, open the Windows folder, open th
e
> Syswow64 folder,
> and then start Perfmon.exe.
> I followed and can see the monitors on the local machine using the 64bit
> perfmon. (they do not show on the stat > run > perfmon
> The question: Any ideas how to force the 64bit monitors to present
> themselves rather then the 32bit - which do not have the sql perf monitirs
.
> Heeeelp.
> Thanks

Performance Monitors on 64bit OS

I am using a 3rd party tool to bind to the perf monitors, with no success. In
the event log:
Windows cannot open the 32-bit extensible counter DLL MSSQLSERVER in a
64-bit environment.
Contact the file vendor to obtain a 64-bit version.
Alternatively, you can open the 32-bit extensible counter DLL by using the
32-bit version
of Performance Monitor. To use this tool, open the Windows folder, open the
Syswow64 folder,
and then start Perfmon.exe.
I followed and can see the monitors on the local machine using the 64bit
perfmon. (they do not show on the stat > run > perfmon
The question: Any ideas how to force the 64bit monitors to present
themselves rather then the 32bit - which do not have the sql perf monitirs.
Heeeelp.
ThanksOK - I have got myself mixed up.
I need the 32bit (Syswow64) perfmon to respond to the 3rd party software
rather than the 64bit.
Cheers
"Mike Duncan" wrote:
> I am using a 3rd party tool to bind to the perf monitors, with no success. In
> the event log:
> Windows cannot open the 32-bit extensible counter DLL MSSQLSERVER in a
> 64-bit environment.
> Contact the file vendor to obtain a 64-bit version.
> Alternatively, you can open the 32-bit extensible counter DLL by using the
> 32-bit version
> of Performance Monitor. To use this tool, open the Windows folder, open the
> Syswow64 folder,
> and then start Perfmon.exe.
> I followed and can see the monitors on the local machine using the 64bit
> perfmon. (they do not show on the stat > run > perfmon
> The question: Any ideas how to force the 64bit monitors to present
> themselves rather then the 32bit - which do not have the sql perf monitirs.
> Heeeelp.
> Thanks|||I have been searching the network and I have found nothing to answer
this question. I am having the same problem. Does anybody have an
idea?
ej
Mike Duncan wrote:
> I am using a 3rd party tool to bind to the perf monitors, with no success. In
> the event log:
> Windows cannot open the 32-bit extensible counter DLL MSSQLSERVER in a
> 64-bit environment.
> Contact the file vendor to obtain a 64-bit version.
> Alternatively, you can open the 32-bit extensible counter DLL by using the
> 32-bit version
> of Performance Monitor. To use this tool, open the Windows folder, open the
> Syswow64 folder,
> and then start Perfmon.exe.
> I followed and can see the monitors on the local machine using the 64bit
> perfmon. (they do not show on the stat > run > perfmon
> The question: Any ideas how to force the 64bit monitors to present
> themselves rather then the 32bit - which do not have the sql perf monitirs.
> Heeeelp.
> Thanks

Performance Monitoring?

My SQL Server 2000 system is pegged. Disk activity is maxed out and system is very unresponsive. Several people have database tasks running through this system and I'm pretty sure there is a single application that is the culprit and I'd like to identify which one.

Does anyone have any practical tips on using "Process Info" in Enterprise Manager? What units are CPU and Physical I/O displayed in? Why does the column sort on these fields not work as expected?

Do I just pick the process with the largest Physical I/O and assume that's the problem?Use profiler...but I'd look for a high cpu and no I/O

That would be a process stuck in a loop, or a very labor intensive in memory process (less likely)

Although I did build 1 like that once...extremeley quick compared to the I/O version...took 1/2 hour...22 hours with I/O...

Performance monitoring tools

Hi,
We have performance issues in our system and it is mainly sql server related
. The third party system we have is archaic and code is written for SQL Serv
er 6.5. It has indices for multiple fields and all the data and index are in
one file. The size s almos
t 50 GB, most of them occupied by CHAR field without any valuable data. The
system created numerous table locks and one part of the system totally locks
out if a user is deleting from a table and other users are trying to access
the system (TABLOCK s). Se
arches take long time and system is crawling at peak time (2-3 pm) when we h
ave more than 1100 connections. Since its a third party tool we have limited
control over code change and they never do any support. The management is a
sking us for better results
. In the immediate future we want to buy a monitoring tool.
Does anybody know the best monitoring tool around?
We are planning to split the data file into multiple files. Split indices al
so into a file. Move the tempdb into another file and keep in a different ra
id array. Please help me with suggestions to improve the server setup.
Thanks. Sorry for writing a long mail because this is getting serious.
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine sup
ports Post Alerts, Ratings, and Searching.Hi,
Are you executing UPDATE STATISTICS on al tables inside the database. If
not please plan for that. As well as use the
DBCC SHOWCONTIG to identify the tables fragmented and use DBCC DBREINDEX to
remove the fragmentation. THis will increase
the performace of you database.
You could use the NT performance monitor tools to identify the DISK QUEUE
LENGTH / DISK (I/O), CPU USage , Memory usage.
If yur Disk I/O is very huge plan to split the fkes in to a diffrent RAID
array.
see the link http://www.sql-server-performance.com/ and check for
PERFORMANCE MONITOR. There you have articles for each counters and usage.
Thanks
Hari
MCDBA
<ivnavin> wrote in message news:O2AnvurcEHA.1656@.TK2MSFTNGP09.phx.gbl...
> Hi,
> We have performance issues in our system and it is mainly sql server
related. The third party system we have is archaic and code is written for
SQL Server 6.5. It has indices for multiple fields and all the data and
index are in one file. The size s almost 50 GB, most of them occupied by
CHAR field without any valuable data. The system created numerous table
locks and one part of the system totally locks out if a user is deleting
from a table and other users are trying to access the system (TABLOCK s).
Searches take long time and system is crawling at peak time (2-3 pm) when we
have more than 1100 connections. Since its a third party tool we have
limited control over code change and they never do any support. The
management is asking us for better results. In the immediate future we want
to buy a monitoring tool.
> Does anybody know the best monitoring tool around?
> We are planning to split the data file into multiple files. Split indices
also into a file. Move the tempdb into another file and keep in a different
raid array. Please help me with suggestions to improve the server setup.
> Thanks. Sorry for writing a long mail because this is getting serious.
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
supports Post Alerts, Ratings, and Searching.|||sounds like there may be indexing problems...
Try using the index tuning wizard inside SQL profiler. It should be
documented in Books on line... If there are locking, table scan issues, the
physical stuff you are doing might not make much difference.
You can create new indexes ( if you discover it is necessary) without
interfering with the third party company's code...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
<ivnavin> wrote in message news:O2AnvurcEHA.1656@.TK2MSFTNGP09.phx.gbl...
> Hi,
> We have performance issues in our system and it is mainly sql server
related. The third party system we have is archaic and code is written for
SQL Server 6.5. It has indices for multiple fields and all the data and
index are in one file. The size s almost 50 GB, most of them occupied by
CHAR field without any valuable data. The system created numerous table
locks and one part of the system totally locks out if a user is deleting
from a table and other users are trying to access the system (TABLOCK s).
Searches take long time and system is crawling at peak time (2-3 pm) when we
have more than 1100 connections. Since its a third party tool we have
limited control over code change and they never do any support. The
management is asking us for better results. In the immediate future we want
to buy a monitoring tool.
> Does anybody know the best monitoring tool around?
> We are planning to split the data file into multiple files. Split indices
also into a file. Move the tempdb into another file and keep in a different
raid array. Please help me with suggestions to improve the server setup.
> Thanks. Sorry for writing a long mail because this is getting serious.
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
supports Post Alerts, Ratings, and Searching.

Performance monitoring tools

Hi,
We have performance issues in our system and it is mainly sql server related. The third party system we have is archaic and code is written for SQL Server 6.5. It has indices for multiple fields and all the data and index are in one file. The size s almos
t 50 GB, most of them occupied by CHAR field without any valuable data. The system created numerous table locks and one part of the system totally locks out if a user is deleting from a table and other users are trying to access the system (TABLOCK s). Se
arches take long time and system is crawling at peak time (2-3 pm) when we have more than 1100 connections. Since its a third party tool we have limited control over code change and they never do any support. The management is asking us for better results
. In the immediate future we want to buy a monitoring tool.
Does anybody know the best monitoring tool around?
We are planning to split the data file into multiple files. Split indices also into a file. Move the tempdb into another file and keep in a different raid array. Please help me with suggestions to improve the server setup.
Thanks. Sorry for writing a long mail because this is getting serious.
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.
Hi,
Are you executing UPDATE STATISTICS on al tables inside the database. If
not please plan for that. As well as use the
DBCC SHOWCONTIG to identify the tables fragmented and use DBCC DBREINDEX to
remove the fragmentation. THis will increase
the performace of you database.
You could use the NT performance monitor tools to identify the DISK QUEUE
LENGTH / DISK (I/O), CPU USage , Memory usage.
If yur Disk I/O is very huge plan to split the fkes in to a diffrent RAID
array.
see the link http://www.sql-server-performance.com/ and check for
PERFORMANCE MONITOR. There you have articles for each counters and usage.
Thanks
Hari
MCDBA
<ivnavin> wrote in message news:O2AnvurcEHA.1656@.TK2MSFTNGP09.phx.gbl...
> Hi,
> We have performance issues in our system and it is mainly sql server
related. The third party system we have is archaic and code is written for
SQL Server 6.5. It has indices for multiple fields and all the data and
index are in one file. The size s almost 50 GB, most of them occupied by
CHAR field without any valuable data. The system created numerous table
locks and one part of the system totally locks out if a user is deleting
from a table and other users are trying to access the system (TABLOCK s).
Searches take long time and system is crawling at peak time (2-3 pm) when we
have more than 1100 connections. Since its a third party tool we have
limited control over code change and they never do any support. The
management is asking us for better results. In the immediate future we want
to buy a monitoring tool.
> Does anybody know the best monitoring tool around?
> We are planning to split the data file into multiple files. Split indices
also into a file. Move the tempdb into another file and keep in a different
raid array. Please help me with suggestions to improve the server setup.
> Thanks. Sorry for writing a long mail because this is getting serious.
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
supports Post Alerts, Ratings, and Searching.
|||sounds like there may be indexing problems...
Try using the index tuning wizard inside SQL profiler. It should be
documented in Books on line... If there are locking, table scan issues, the
physical stuff you are doing might not make much difference.
You can create new indexes ( if you discover it is necessary) without
interfering with the third party company's code...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
<ivnavin> wrote in message news:O2AnvurcEHA.1656@.TK2MSFTNGP09.phx.gbl...
> Hi,
> We have performance issues in our system and it is mainly sql server
related. The third party system we have is archaic and code is written for
SQL Server 6.5. It has indices for multiple fields and all the data and
index are in one file. The size s almost 50 GB, most of them occupied by
CHAR field without any valuable data. The system created numerous table
locks and one part of the system totally locks out if a user is deleting
from a table and other users are trying to access the system (TABLOCK s).
Searches take long time and system is crawling at peak time (2-3 pm) when we
have more than 1100 connections. Since its a third party tool we have
limited control over code change and they never do any support. The
management is asking us for better results. In the immediate future we want
to buy a monitoring tool.
> Does anybody know the best monitoring tool around?
> We are planning to split the data file into multiple files. Split indices
also into a file. Move the tempdb into another file and keep in a different
raid array. Please help me with suggestions to improve the server setup.
> Thanks. Sorry for writing a long mail because this is getting serious.
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
supports Post Alerts, Ratings, and Searching.

Saturday, February 25, 2012

Performance of "text" versus "image" datatypes (SS2000)

Performance of "SELECT COUNT(*) FROM X WHERE Y" queries

performance objects for SQLServer disappeared

performance objects for SQLServer disappeared

Performance Object SQL Server

Performance Object SQL Server

Performance Object SQL Server

Performance object list is missing

Performance object list is missing

Performance object list is missing

Performance object "SQL Server" does not exist

Performance mystery -- SP vs script

Performance Monitors with SAN

Performance Monitors Start/Stop 7AM

Performance Monitors Start/Stop 7AM

Performance Monitors Start/Stop 7AM

performance monitors on the SQL 2005

performance monitors on the SQL 2005

performance monitors on the SQL 2005

Performance Monitors on 64bit OS

Performance Monitors on 64bit OS

Performance Monitors on 64bit OS

Performance Monitoring?

Performance monitoring tools

Performance monitoring tools

performance monitoring

Blog Archive

About Me