Diagnosing Performance Problem?

I have a database server that has is acting very sluggish. Using the performance monitor, I can see that disk use is steadily at 100% usage.

The system is so overburdened that attempting to do even basic tasks (open folders, look in Enterprise Manager, get screen repaints) has become impractical.

Even if I could get to the Enterprise Manager "Process Info" display, what would I look for to tell me what is wrong?From a remote PC, use Profiler to monitor all queries with over say 1000 reads. Copy those queries out to Query Analyzer, and see if you can get some indexes on the underlying tables to speed them up.

Another thing to check is how much memory is in the box? SQL Server will default to taking all it wants, but if you have only 64MB in your box, then you are in for a world-o-hurt.|||Originally posted by MCrowley
From a remote PC, use Profiler to monitor all queries with over say 1000 reads.

How do I do this? What is Profiler?

thanks for the feedback!|||Originally posted by RogerWilco
How do I do this? What is Profiler?

thanks for the feedback!

Found it. This is definitely helping.

Thankfully, we just hired a real DBA who starts Thursday.

Thanks again!

Device error or device off-line. ?

Hi to All

when i m trying to execute following code

backup database web
to disk = 'c:\inetpub\wwwroot\backup\mybakup.bak'
with format

I m Getting Error like :

Server: Msg 3201, Level 16, State 1, Line 1
Cannot open backup device 'c:\inetpub\wwwroot\backup\mybakup.bak'. Device error or device off-line. See the SQL Server error log for more details.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

This error is Generated only when i m trying to access folders within "wwwroot" but not in any other folders , even command runs success fully for "wwwroot" folder . !!

but not for any subfolders of wwwroot.

Can Any One Help Me ?

Please follow the post in the other group, you don′t have to mulitpost as we the groups are all monitored.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Device error or device off-line ?

Hi to All

when i m trying to execute following code

backup database web
to disk = 'c:\inetpub\wwwroot\backup\mybakup.bak'
with format

I m Getting Error like :

Server: Msg 3201, Level 16, State 1, Line 1
Cannot open backup device 'c:\inetpub\wwwroot\backup\mybakup.bak'. Device error or device off-line. See the SQL Server error log for more details.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

This error is Generated only when i m trying to access folders within "wwwroot" but not in any other folders , even command runs success fully for "wwwroot" folder . !!

but not for any subfolders of wwwroot.

Can Any One Help Me ?

What does show

xp_cmdshell "dir c:\inetpub\wwwroot\backup"

?

|||

DO the subfolders inherit the permission from the wwwroot folder ? Make sure that this isn′t a permission problem. What does (like the message says the error log tell you about the problem?

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

i m using Sql Server 2000 (sp3);

command

xp_cmdshell "dir c:\inetpub\wwwroot\backup"

gives following error:

Server: Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'xp_cmdshell'.

|||

All Permission have been set properly.

but error is still as it is.

|||

the xp_cmdshell is located in the master database, so you have to prefix the database name or need to have your curretn context in the master database.

master.xp_cmdshell "dir c:\inetpub\wwwroot\backup"

or

Use master
GO
xp_cmdshell "dir c:\inetpub\wwwroot\backup"

HTH, JEns Suessmeyer.

http://www.sqlserver2005.de

|||

Hi,

sometime people think that the path within any TSQL commands can be also used locally, so if you are working in a client server scenario and you issue the command BACKUP DATABASE with some path and filename (with absolute drive letters), the drive letters apply to the server not the client. If you want to reach a destination on the client machine, you have to copy it via a UNC share.

Don′t know if that applies to you, just to let you know. If this is not the case, please see if the listing of the directory comes back with a listing of files (then it really HAS to be a permission problem) or it comes back with an error that the directory doesn′t exists.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Developer installation fails - does not prompt for Disk 2!

I have tried this many times....cleaned all previous MS SQL installations...rebooted....all with the same result below.

I insert Disc 1 of the Developer edition and all is find until it gets to the main installation...then OWC fails ("Setup cannot find the files specified") although I have an open Internet connection from where it can be downloaded.

Then the main Database server and Integration services installation fails, and other components succeed. When I click the error message it says "Please insert Disc 2" - although the main installation never prompts for Disc 2 (which are the Tools I believe).

Even if I install Disc 2, the installation never succeeds - and the main componets I am interested in - Database server and Integration services - always fails.

I have used the developer edition with many operating systems OWC is not a requirement for installing it, I have to tell you installing SQL Server from CD is not good practice. You need to create a folder in your C drive and copy the contents of the CDs into that folder and your installation will complete because all your files are in one place. If you need OWC in your development environment you need to download that to the folder before running the installer. Hope this helps.

Determining what causes tempdb to autogrow

I have a SQL server with over 15 user databases on it. Tempdb autogrows to
the point that it monopolizes all available disk space (I just recently
shrank it from 36 GB to free up space). Does anyone have any tips on how to
determine what process are causing so much space usage for my tempdb? It is
perplexing...I used sysmon to track when log usage increases and I try to
correlate that with profile traces in which I capture 'text like '%#%'.
This is not the best way to do this so I was wondering if anyone had any
suggestions.
Rod,
Use perfmonitor.
Performance object - "SQLServer: Access methods"
Counters - Workfiles Created/sec,Worktables created/sec,Worktables From
cache Ratio and also anything related.
Performance object - "SQLServer:databases"
Counters - Active transactions, datafile size and also anything related.
Here is some related info:
'TechNet Support WebCast: Administering and maintaining the tempdb system
database in Microsoft SQL Server'
http://support.microsoft.com/default...&Product=sql2k
Dinesh
SQL Server MVP
--
SQL Server FAQ at
http://www.tkdinesh.com
"Rod Bautista" <rod.bautista@.adam-us.com> wrote in message
news:uLrNl#GOEHA.2716@.tk2msftngp13.phx.gbl...
> I have a SQL server with over 15 user databases on it. Tempdb autogrows
to
> the point that it monopolizes all available disk space (I just recently
> shrank it from 36 GB to free up space). Does anyone have any tips on how
to
> determine what process are causing so much space usage for my tempdb? It
is
> perplexing...I used sysmon to track when log usage increases and I try to
> correlate that with profile traces in which I capture 'text like '%#%'.
> This is not the best way to do this so I was wondering if anyone had any
> suggestions.
>
|||Some information here as well
http://www.aspfaq.com/2446
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Rod Bautista" <rod.bautista@.adam-us.com> wrote in message
news:uLrNl%23GOEHA.2716@.tk2msftngp13.phx.gbl...
>I have a SQL server with over 15 user databases on it. Tempdb autogrows to
> the point that it monopolizes all available disk space (I just recently
> shrank it from 36 GB to free up space). Does anyone have any tips on how
> to
> determine what process are causing so much space usage for my tempdb? It
> is
> perplexing...I used sysmon to track when log usage increases and I try to
> correlate that with profile traces in which I capture 'text like '%#%'.
> This is not the best way to do this so I was wondering if anyone had any
> suggestions.
>

Determining what causes tempdb to autogrow

I have a SQL server with over 15 user databases on it. Tempdb autogrows to
the point that it monopolizes all available disk space (I just recently
shrank it from 36 GB to free up space). Does anyone have any tips on how to
determine what process are causing so much space usage for my tempdb? It is
perplexing...I used sysmon to track when log usage increases and I try to
correlate that with profile traces in which I capture 'text like '%#%'.
This is not the best way to do this so I was wondering if anyone had any
suggestions.Rod,
Use perfmonitor.
Performance object - "SQLServer: Access methods"
Counters - Workfiles Created/sec,Worktables created/sec,Worktables From
cache Ratio and also anything related.
Performance object - "SQLServer:databases"
Counters - Active transactions, datafile size and also anything related.
Here is some related info:
'TechNet Support WebCast: Administering and maintaining the tempdb system
database in Microsoft SQL Server'
http://support.microsoft.com/default.aspx?scid=kb;en-us;834846&Product=sql2k
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Rod Bautista" <rod.bautista@.adam-us.com> wrote in message
news:uLrNl#GOEHA.2716@.tk2msftngp13.phx.gbl...
> I have a SQL server with over 15 user databases on it. Tempdb autogrows
to
> the point that it monopolizes all available disk space (I just recently
> shrank it from 36 GB to free up space). Does anyone have any tips on how
to
> determine what process are causing so much space usage for my tempdb? It
is
> perplexing...I used sysmon to track when log usage increases and I try to
> correlate that with profile traces in which I capture 'text like '%#%'.
> This is not the best way to do this so I was wondering if anyone had any
> suggestions.
>|||Some information here as well
http://www.aspfaq.com/2446
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Rod Bautista" <rod.bautista@.adam-us.com> wrote in message
news:uLrNl%23GOEHA.2716@.tk2msftngp13.phx.gbl...
>I have a SQL server with over 15 user databases on it. Tempdb autogrows to
> the point that it monopolizes all available disk space (I just recently
> shrank it from 36 GB to free up space). Does anyone have any tips on how
> to
> determine what process are causing so much space usage for my tempdb? It
> is
> perplexing...I used sysmon to track when log usage increases and I try to
> correlate that with profile traces in which I capture 'text like '%#%'.
> This is not the best way to do this so I was wondering if anyone had any
> suggestions.
>

Determining what causes tempdb to autogrow

I have a SQL server with over 15 user databases on it. Tempdb autogrows to
the point that it monopolizes all available disk space (I just recently
shrank it from 36 GB to free up space). Does anyone have any tips on how to
determine what process are causing so much space usage for my tempdb? It is
perplexing...I used sysmon to track when log usage increases and I try to
correlate that with profile traces in which I capture 'text like '%#%'.
This is not the best way to do this so I was wondering if anyone had any
suggestions.Rod,
Use perfmonitor.
Performance object - "SQLServer: Access methods"
Counters - Workfiles Created/sec,Worktables created/sec,Worktables From
cache Ratio and also anything related.
Performance object - "SQLServer:databases"
Counters - Active transactions, datafile size and also anything related.
Here is some related info:
'TechNet Support WebCast: Administering and maintaining the tempdb system
database in Microsoft SQL Server'
http://support.microsoft.com/defaul...6&Product=sql2k
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Rod Bautista" <rod.bautista@.adam-us.com> wrote in message
news:uLrNl#GOEHA.2716@.tk2msftngp13.phx.gbl...
> I have a SQL server with over 15 user databases on it. Tempdb autogrows
to
> the point that it monopolizes all available disk space (I just recently
> shrank it from 36 GB to free up space). Does anyone have any tips on how
to
> determine what process are causing so much space usage for my tempdb? It
is
> perplexing...I used sysmon to track when log usage increases and I try to
> correlate that with profile traces in which I capture 'text like '%#%'.
> This is not the best way to do this so I was wondering if anyone had any
> suggestions.
>|||Some information here as well
http://www.aspfaq.com/2446
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Rod Bautista" <rod.bautista@.adam-us.com> wrote in message
news:uLrNl%23GOEHA.2716@.tk2msftngp13.phx.gbl...
>I have a SQL server with over 15 user databases on it. Tempdb autogrows to
> the point that it monopolizes all available disk space (I just recently
> shrank it from 36 GB to free up space). Does anyone have any tips on how
> to
> determine what process are causing so much space usage for my tempdb? It
> is
> perplexing...I used sysmon to track when log usage increases and I try to
> correlate that with profile traces in which I capture 'text like '%#%'.
> This is not the best way to do this so I was wondering if anyone had any
> suggestions.
>