Thursday, July 11, 2013

[MS SQL Server] SQL Server Memory Usage

[MS SQL Server] SQL Server Memory Usage

SQL Server Memory Usage

Posted: 11 Jul 2013 02:02 AM PDT

I observed that in my Production Server (SQL Server 2005)PF Usage: 496 GBTotal RAM: 511 GBAvailable RAM: 8 GB Database Size: 1 TBSQL Server process (SQLSVRVR.EXE) MEMORY USAGE in Task Manager: 493 GBist it normal ? or we need to take any action. Can you please any one suggest me ?

Piecemeal restore takes forever

Posted: 10 Jul 2013 10:42 PM PDT

Hi EveryoneI have a 1.1TB database with 700GB of data on a SQL Server 2008 R2 SP1 instance. Most data is row compressed and in 2 partitioned tables. A full backup is taken daily to 4 files on a NAS as part of the daily maintenance routine (which also includes index fragmentation, updating statistics and an integrity check directly before the full backup and in that order). The backup is also compressed.On a testserver I want to do a piecemeal restore, starting with the primary filegroup (the partitioned tables are in other filegroups). The primary filegroup file is 9GB and has about 3GB of data. The transaction logfile is 16GB. I know, with a restore, the transaction logfile is being zeroed which could take some time. On the other hand, the empty space in the primary data file is not being zeroed during a restore operation when Instant File Initialization is enabled. This is the case on my testserver. The problem is that the piecemeal restore takes a very long time, about 6 hours. I investigated and tried a few things to speed up the restore. Here they are:- Check if Instant File Initialization is enabled, it is.- Checking the performance of the whole chain (NAS, network, SAN), by restoring another backup with approximately the same size (3GB) from the same NAS to the same testserver over the same network. The restore was done in 2-4 minutes.- Checking the number of virtual logfiles, which is 41 and thus ok.- Take a partial backup and restore that one partially. This restore took 2-4 minutes. My conclusion: creating the mdf and ldf files is not the problem.- Altered the daily maintenance routine by adding a transactionlog backup after the integrity check and before the full backup, hoping that the transactions from the transactionlog caused by the earlier operations, are left out from the full backup. I'm not sure this makes sense, but to my understanding when SQL Server takes a backup, the transactions in the transaction log are also included for the redo/undo fase when restoring a the backup. Also, I opened the Performance Monitor to see disk activity during the restore operation. There is only disk activity during the beginning of the restore operation. Furthermore, when I execute sp_WhoIsActive during the restore operation, I see that the restore session is suspended (wait_info: BACKUPTHREAD) while network traffic stays at maximum. The CPU's are also processing 'something'. I'm beginning to suspect the restore operation is scanning every page in the full backup files somehow. Is there someone who knows if this is true or, if not, knows the true reason why the piecemeal restore is so slow?Thanks

Frozen process

Posted: 10 Jul 2013 04:56 PM PDT

Hi,I have a process in "killed/rollback" state for a long time. I found two workarounds for this:1. Restart the SQL instance2. If the process that is running is external to SQL server i.e. OSQL, sqlmaint, and if you do the KILL SPID on it, it might not finish the rollback.To avoid the server restart of the SQL Service, do not run the KILL SPID within the SQL Server instead kill the process at the OS level and the SPID from within the SQL will disappear.The fist solution is an easy one, but not desirable. The second is tricky to find out.By running this query:DECLARE @v1 VARBINARY(64)SELECT @v1 = sql_handle FROM sys.dm_exec_requests WHERE session_id = 110SELECT * FROM sys.dm_exec_sql_text(@v1)I found that the process was executing this command:DROP DATABASE MyDBName_dbss -- this is a database snapshotAt the time when I looked the snapshot did not exist.Does SQL Server run something external when dropping database snapshots? If so, how can I determine the process at the OS level?Or may be, how can I change the dropping of the snapshots, so I don't end up in a situation like this?Thanks.

Memory Issues on production sql server

Posted: 10 Jul 2013 07:21 AM PDT

Please help me in this situation.Total memory on box : 10 GBSingle node, non clustered production boxMin memory is set to 0Max Memory is set to 8 GBFrom last month the box Memory is constantly at 95%CPU usage is very lowhere is the screen shot from the task manager[img]http://img838.imageshack.us/img838/1104/p77j.png[/img]How to find Is SQL server using the complete 8GB ?Management is asking to reduce the SQL server memory setting to 7GBIs this fine to decrease the memory limit. Please advice.

Error uploading Files

Posted: 10 Jul 2013 09:08 AM PDT

One of user is complaining about a reporting server issue, whenever they try to upload a file, they are getting the below error....i checked for space issue......it's not the space issue, i am kind of lost as to what to check next. Please see the error message below and advise if you have any idea. Client found response content type of 'text/html; charset=utf-8', but expected 'text/xml'. The request failed with the error message: -- <html> <head> <title> SQL Server Reporting Services </title><meta name="Generator" content="Microsoft SQL Server Reporting Services 9.00.5069.00" /> <meta name="HTTP Status" content="500" /> <meta name="ProductLocaleID" content="9" /> <meta name="CountryLocaleID" content="1033" /> <style> BODY {FONT-FAMILY:Verdana; FONT-WEIGHT:normal; FONT-SIZE: 8pt; COLOR:black} H1 {FONT-FAMILY:Verdana; FONT-WEIGHT:700; FONT-SIZE:15pt} LI {FONT-FAMILY:Verdana; FONT-WEIGHT:normal; FONT-SIZE:8pt; DISPLAY:inline} .ProductInfo {FONT-FAMILY:Verdana; FONT-WEIGHT:bold; FONT-SIZE: 8pt; COLOR:gray} A:link {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; COLOR:#3366CC; TEXT-DECORATION:none} A:hover {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; COLOR:#FF3300; TEXT-DECORATION:underline} A:visited {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; COLOR:#3366CC; TEXT-DECORATION:none} A:visited:hover {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; color:#FF3300; TEXT-DECORATION:underline} </style> </head><body bgcolor="white"> <h1> Reporting Services Error<hr width="100%" size="1" color="silver" /> </h1><ul> <li>An internal error occurred on the report server. See the error log for more details. (rsInternalError) <a href="http://go.microsoft.com/fwlink/?LinkId=20476&EvtSrc=Microsoft.ReportingServices.Diagnostics.Utilities.ErrorStrings&EvtID=rsInternalError&ProdName=Microsoft%20SQL%20Server%20Reporting%20Services&ProdVer=9.00.5069.00" target="_blank">Get Online Help</a></li><ul> <li>For more information about this error navigate to the report server on the local server machine, or enable remote errors</li> </ul><hr width="100%" size="1" color="silver" /><span class="ProductInfo">SQL Server Reporting Services</span> </ul> </body> --.Thanks,SueTons.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog