SQL server performance tuning-tempdb

[ajjunit84]

Hi All,

We are using SQL Server 2005 .The tempdb size has grown and second thing is that sqlser.exe as checked in Task Manager is taking around 41% CPU and memory utilization is also high.

Can anybody let me know how can I clean up tempdb ,as we don't want to add one more log/data file.

Also ,what should be done to fine tune the SQL performance.

Hope to hear from you soon.

Thanks in Advance

=============

Ajay

[Former Member · Accepted solution]

HI Ajay,

Use the following command and let me know the result.

dbcc shrinkfile('tempdb.dbf',10);

Rgds,

SK

[ajjunit84]

Thanks Rohit for the valuable information.

Indeed the tempdb has lot of free space now.

I am not good with commands ,even the command given earlier ,I am not able to run that.

Do you have any doc/link related to how to use the commands to operate SQL db ,that will be of great help.

Thanks again.

Regards

Ajay

[Former Member]

Just open the SQL Server studio,connect to the database and in the query analyzer

run the command,it will work fine

You can refer to the following link for more commands:

http://www.ss64.com/sql/

Also you can run the commands through SQL Server 2005 Command Line Tool u201CSQLCMDu201D

you can use the following link for that

http://www.databasejournal.com/features/mssql/article.php/3654176/SQL-Server-2005-Command-Line-Tool-...

Let me know if this helps

Rohit

[ajjunit84]

Thanks Rohit.

The MS SQL pfd for tuning is nice.

Subhadip

Can you ps let me know if I need to run the command from DB directory etc.

Can I use SQL studio for the dbcc ,we are running dccc check data base integrity ,may shrink file dbcc is also present there.

Will read teh guide ,but anyou tell me in case why sql.exe is using lot of cpu/memory .We are running multiple instance on the same host ,all using SQL as db.

Meanwhile ,we have restarted the system as well.

Regards

Ajay

Edited by: Ajay Sandal on Feb 17, 2009 3:20 PM

Edited by: Ajay Sandal on Feb 17, 2009 3:21 PM

[Former Member]

Hello Ajay

Since you have restarted the system,I dont think u will need to shrink the file now

But in longer run u can go for this,you can run it from SQL server studio

Rohit

[Former Member]

Hello Ajay,

The process that you have mentioned is SQLSERVR.EXE.

well you should not worry about this:

"The Microsoft SQL Server is displayed as process SQLSERVR. A high CPU consumption by SQLSERVR is normal."

Refer to SAP note 155402

Rohit

[Former Member]

Hello Ajay,

Happy to know that you are able to shrink the tempdb file. Restart will not shrink your file. Shrinking operation freed unused space from tempdb.

You can run the command from SQL Server Management Studio and open the new query from the tab menu and can execute the command.

Rgds,

SK

[Former Member]

Hello Ajay,

You can not clean Temp DB as such,

It will get free whenever you start and stop the system.

SAP recommends for temp DB that it should be at least twice the size of largest table.

regarding performance tuning its a very generic question,can u be very specific in what areas of database u want tuning.

Also microsoft has released "

SAP with Microsoft SQL Server 2005:

Best Practices for High Availability, Maximum Performance, and Scalability

SQL Server Technical Article"

It is very useful,i have that woth myself but it is available on site also.I will try to get u the link and more info.

Rohit

[Former Member]

Hello AJay,

Regarding Temp DB,please go through the following articles,they are very useful

1. http://msdn.microsoft.com/en-us/library/ms190768.aspx

2. http://msdn.microsoft.com/en-us/library/ms175527.aspx-Temp DB perfromance

For SAP,Microsoft has release a paper,this is the link:

http://download.microsoft.com/download/d/9/4/d948f981-926e-40fa-a026-5bfcf076d9b9/SAP_SQL2005_Best%2...

Let me know of any questions

Rohit