Search This Blog

Sunday, February 17, 2008

Understanding Memory issues through Perfmon:Page faults

I have been using Perfmon almost daily, but never took pain to learn how to use it efficiently until I came across a situation …In this part we will discuss using perfmon to see memory bottleneck.
Before we proceed forward let us go through few definitions so that the rest of the stuff is easily understood.

Virtual memory: Its actually virtual …and from here the concept of paging comes in to picture .On a 32 bit machine its 2 gigs for user process by default ….So, if your RAM is 512 MB, the remaining stuff (I mean greater that 512 MB) that’s going to come from Virtual memory will page out to disk .SO operations worth 2 gigs will happen in VAS (VM) and as a storage RAM will be used and the moment RAM limit is exceeded …..Paging/Swapping .

Page: Committed data in VAS is written to Page tables .From there it goes to RAM or Pagefile .This data is written in to pages (in bytes) and its size is processor dependent. this page is fitted in to page frame in RAM…it is essential that , that frame must be filled with zeros before a page from disk is inserted into page frame .zeroing is essential as the page before zeroing may have information from any other process ..So when we restart Windows this zeroing activity takes place and complete by the virtual memory manager before the Windows comes up..And if there is some problem during zeroing then………….. :D …we will discuss some other time …

Page file: a space in the HDD to be used to save committed memory objects.

Reserved memory: the memory quota given to a process by the VM manager .So the process first has to consume this before the next quota is allocated …

Committed memory: The chunk from reserved memory that is actually being used by the process. Remember that whenever memory is committed the same amount of space is created on the pagefile so that if needed it can be flushed to disk.

Working Set: Physical memory assigned to a process by VM manager (or say OS).It will be always less than Private Bytes.

Page Fault : When the page needed is not found in the working set of the process but its with in the RAM (may be in some cache) its called as Soft page fault .But when the page is not at all found in the RAM , its paged out and called as Hard page fault.

So now, it’s sure that whenever there is a memory issue there will be high (hard) Page fault rate...If there is a constant high hard page fault rate it means that there is some data not found in the RAM for the working set of that process .So , it has to be fetched from the disk (which is going to be costly).And the hard page faults cause Paging .So is paging harmful ….No (we cannot avoid it ), but the excess of it is a signal that something is going wrong .Generally I have seen that keeping the page file to be equal to 1.5 times of RAM is ok ..Not bad.

In case of memory issue the counters that we need to see in Perfmon are Memory and Process...

The Sub counters for memory counter are: Page Faults/sec, Page Reads/sec, Page Writes/sec, Pages Input/sec, Pages Output/sec, Available bytes and nonpaged pool bytes.

The Sub counters for Process counter are: Page Faults/sec, working set, Private Bytes and Page File Bytes

So, % Soft page faults = ((Page fault/sec - Pages input/sec) x 100)/Page faults/sec

Now, if there are more hard page faults, there should be more pages output/sec (no of pages sent to disk to make room for the pages coming form disk due to hard page fault) since there is a limited working set and hence there has to be some space for the pages coming from the disk (hard page faults) ...Pages read/sec and pages input/sec should be almost similar but can be a bit different …So these counters can tell you the system story..One more counter that I forgot to mention is Available Bytes .Its the no. of bytes left for the process to allocate. To see how much SQL Server shares you can see Page Faults/sec and proportionate it with Total Page faults. Generally, hard page fault of 10% to 15% should be tolerated …Before, we totally blame memory let’s check the Disk performance as well...

Subtract Memory:Page reads/sec from logical disk:disk reads/sec .If the output is small (+ or -) then the memory is the bottleneck because this actually means that all the disk reading is done to find the faulting pages .Also if disk output/sec is high then you can check by finding the difference of disk output/sec and Disk write bytes/sec ÷ 4096 ßintel and fine its percentage in total Disk write bytes/sec

Similarly, you can then correlate it to SQL Server as well …..Once you are sure that memory is the bottleneck then we can proceed with the troubleshooting steps...
Next time we will try to dig into memory issues including memory leaks and also perfmon counters specific to SQL Server …

As always, your comments are welcome...

Wednesday, February 13, 2008

Shall We use /PAE and /3GB switches together ..my views

Physical Address Extension (PAE) refers to a feature of x86 and x86-64 processors that allows the OS to see more than regular 4 gigs of physical RAM on a 32-bit machine.To do that we need to specify /PAE switch in the boot.ini file and reboot the machine so that this setting takes effect.

So when you enable PAE ,the physical address size is increased from 32 bits to 36 bits which is equal to 64 GB. Note that the 32-bit size of virtual address is never changed, and it remains 4 GB.The OS uses PTE ( Page Table Entries) to map this 4 GB address space onto the 64 GB of total memory.Now any APP which needs access to more than 4 GiB of memory,has to have some special mechanism and on MS Windows this mechanism is called Address Windowing Extensions (AWE), while on Unix-like systems a variety of tricks are used, such as using mmap() to map regions of a file into and out of the address space as neededbut not yet adopted as a standard.

AWE is an MS API that allows the apps to access more physical memory than available in VAS ..The process through which it maps the VAS to physical memory is called as Windowing ...Not all the applications are AWE enabled ..but SQL Server is ..However , for doing that we need to have the locked pages in memory privilege enabled .One more benefit of using AWE in SQL Server is that the memory being allocated using AWE will not be written in Page files ..So,AWE can prevent Windows from swapping sensitive data from RAM to pagefile.

Now , I am pretty sure that we all know /3GB setting but just wanted to convey that with this setting the physical memory limit on Windows Server 2003 is 16 GB only ..So even if you have more than 16GB RAM you cannot see/use it :D...thats because it trims the PTE table rows ..

Now comes the real question ....Should we use /PAE and /3GB switch together .....well one negative factor i have already mentioned above. Secondly just for 1 GB I dont feel it would be worth .....Its set on my machine and i have no issues :)..

However , I have seen a few cases where these 2 combinations creates situations like Blue screen or black screen on machine upon boot up .....that is something because the OS goes for a toss while going through ZEROING activity ..But i think this stuff still needs some good research ...So, I do not say dont use it , but surely test it first .

So , summary is that I am not totally against it but i wont prefer it ....
Your choice :D .......




Tuesday, February 12, 2008

What all objects are consuming memory (and how much)

To determine what plans are in the cache and how often they're used we can use sys.dm_os_memory_cache_counters dm view .

SELECT TOP 6 LEFT([name], 20) as [name], LEFT([type], 20) as [type], [single_pages_kb] + [multi_pages_kb] AS cache_kb, [entries_count]FROM sys.dm_os_memory_cache_counters order by single_pages_kb + multi_pages_kb DESC

here :
CACHESTORE_OBJCP are compiled plans for stored procedures, functions and triggers. CACHESTORE_SQLCP are cached SQL statements or batches that aren't in stored procedures, functions and triggers. This includes any dynamic SQL or raw SELECT statements sent to the server. CACHESTORE_PHDR These are algebrizer trees for views, constraints and defaults. An algebrizer tree is the parsed SQL text that resolves the table and column names.
(you will find these counters in DBCC Memorystatus as well.Infact DBCC Memory Status uses this dm)

Generally you will find that CACHESTORE_SQLCP > CACHESTORE_OBJCP , but if the ratio of one to another is very high then we can say that there are more adhoc plans being run then Stored procedures.That is the reason the sal statements are going in to Plan cache.
You can also monitor the number of data pages in the plan cache using Performance Monitor (PerfMon) using SQLServer:Plan Cache object with the Cache Pages counter. There are instances for SQL Plans (CACHESTORE_SQLCP), Object Plans (CACHESTORE_OBJCP) and Bound Trees (CACHESTORE_PHDR). This will give you the same picture ..for e.g. under bound tree : multiply cache pages by 8. you will get the same output as in dbcc memorystatus and the dm we used above.

After this we can use sys.dm_exec_cached_plans and sys.dm_exec_sql_text dm views to find the queries :
select TOP 100 objtype, usecounts, p.size_in_bytes/1024 'IN KB', LEFT([sql].[text], 100) as [text]from sys.dm_exec_cached_plans pouter apply sys.dm_exec_sql_text (p.plan_handle) sqlORDER BY usecounts DESC

And then we can check the query plans(and size) for the one we have some doubt .

Now , SQL Server memory is primarily used to store data (buffer) and query plans (cache). We will try to find what tables and indexes are in the buffer memory of your server you can use sys.dm_os_buffer_descriptors DMV.Further , the query below can give us total currrent size of buffer pool .

select count(*) AS Buffered_Page_Count ,count(*) * 8192 / (1024 * 1024) as Buffer_Pool_MBfrom sys.dm_os_buffer_descriptors

After we have found the Bufferpool size , we can see which database is using more memory by runnig the query below :

SELECT LEFT(CASE database_id WHEN 32767 THEN 'ResourceDb' ELSE db_name(database_id) END, 20) AS Database_Name, count(*)AS Buffered_Page_Count, count(*) * 8192 / (1024 * 1024) as Buffer_Pool_MBFROM sys.dm_os_buffer_descriptorsGROUP BY db_name(database_id) ,database_idORDER BY Buffered_Page_Count DESC

And then we can go further at object level to see what all objects are consuming memory (and how much) .We can use the query below in each database we wish to :

SELECT TOP 25 obj.[name], i.[name], i.[type_desc], count(*)AS Buffered_Page_Count , count(*) * 8192 / (1024 * 1024) as Buffer_MB -- ,obj.name ,obj.index_id, i.[name]FROM sys.dm_os_buffer_descriptors AS bd INNER JOIN ( SELECT object_name(object_id) AS name ,index_id ,allocation_unit_id, object_id FROM sys.allocation_units AS au INNER JOIN sys.partitions AS p ON au.container_id = p.hobt_id AND (au.type = 1 OR au.type = 3) UNION ALL SELECT object_name(object_id) AS name ,index_id, allocation_unit_id, object_id FROM sys.allocation_units AS au INNER JOIN sys.partitions AS p ON au.container_id = p.hobt_id AND au.type = 2 ) AS obj ON bd.allocation_unit_id = obj.allocation_unit_idLEFT JOIN sys.indexes i on i.object_id = obj.object_id AND i.index_id = obj.index_idWHERE database_id = db_id()GROUP BY obj.name, obj.index_id , i.[name],i.[type_desc]ORDER BY Buffered_Page_Count DESC

Auto Parameterization in SQL Server

Let us look at how and when SQL Server can parameterize dynamic SQL to reuse the query plan.Its through Parsing and compiling that the query plans get in to memory and then may or may not be reused.

Before executing a batch of SQL statements, SQL Server creates an execution plan (or query plan) for those statements. This is also referred to as compiling the SQL statements. One of the benefits of stored procedures is that SQL Serve will reuse compiled query plans.

Here is the example :
DBCC FREEPROCCACHEGO
set showplan_all on
SELECT *FROM Sales.SalesOrderHeaderWHERE SalesOrderID = 56000GO
select stats.execution_count AS exec_count, p.size_in_bytes as [size], [sql].[text] as [plan_text]from sys.dm_exec_cached_plans pouter apply sys.dm_exec_sql_text (p.plan_handle) sqljoin sys.dm_exec_query_stats stats ON stats.plan_handle = p.plan_handleGO

Using the above mentioned DMV's we can find the number of times this plan has been executed, how big the plan is in bytes and the text of the plan.

Notice that the integer 56000 has been replaced by a variable ([@1])in the text of the query plan. Also notice that the list of parameters (only one parameter in this case) has been inserted at the beginning of the query plan. This is called "Simple Parameterization" (It was called "Auto Parameterization" in SQL Server 2000). If we run it twice we'd see the execution count increase to two but there would still only be a single plan.
SQL Server can also parameterize queries if they aren't identical.

lets check this :

DBCC FREEPROCCACHEGO
SELECT *FROM Sales.SalesOrderHeaderWHERE SalesOrderID = 56000GO
SELECT * FROM Sales.SalesOrderHeader WHERE SalesOrderID = 56001GO
select *from Sales.SalesOrderHeaderwhere SalesOrderID = 56002GO
select stats.execution_count AS exec_count, p.size_in_bytes as [size], [sql].[text] as [plan_text]from sys.dm_exec_cached_plans pouter apply sys.dm_exec_sql_text (p.plan_handle) sqljoin sys.dm_exec_query_stats stats ON stats.plan_handle = p.plan_handleGO

Now let's look at something different.

DBCC FREEPROCCACHEGO
SELECT *FROM Sales.SalesOrderHeaderWHERE SalesOrderID = 56000GO
SELECT * FROM AdventureWorks.Sales.SalesOrderHeader WHERE SalesOrderID = 56001GO
declare @i intset @i = 56004SELECT *FROM Sales.SalesOrderHeaderWHERE SalesOrderID = @iGO
select stats.execution_count AS exec_count, p.size_in_bytes as [size], [sql].[text] as [plan_text]from sys.dm_exec_cached_plans pouter apply sys.dm_exec_sql_text (p.plan_handle) sqljoin sys.dm_exec_query_stats stats ON stats.plan_handle = p.plan_handleGO

So,even slight changes in the text of the plan prevent SQL Server from reusing the query plans.If you want SQL Server to parameterize your SQL statements you have three options: stored procedures, sp_executesql or Forced Parameterization. Stored procedures almost always have a query plan created and reused.
Lets try without sp_executesql first
---------------------------------------------
DBCC FREEPROCCACHEGO
SELECT SUM(LineTotal) AS LineTotalFROM Sales.SalesOrderHeader HJOIN Sales.SalesOrderDetail D ON D.SalesOrderID = H.SalesOrderIDWHERE H.SalesOrderID = 56000GO
SELECT SUM(LineTotal) AS LineTotalFROM Sales.SalesOrderHeader HJOIN Sales.SalesOrderDetail D ON D.SalesOrderID = H.SalesOrderIDWHERE H.SalesOrderID = 56001GO
select stats.execution_count AS exec_count, p.size_in_bytes as [size], LEFT([sql].[text], 80) as [plan_text]from sys.dm_exec_cached_plans pouter apply sys.dm_exec_sql_text (p.plan_handle) sqljoin sys.dm_exec_query_stats stats ON stats.plan_handle = p.plan_handleGO

Now we will use sp_executesql
------------------------------------------
Now we will use sp_executesql .
DBCC FREEPROCCACHEGO
EXEC sp_executesql N'SELECT SUM(LineTotal) AS LineTotalFROM Sales.SalesOrderHeader HJOIN Sales.SalesOrderDetail D ON D.SalesOrderID = H.SalesOrderIDWHERE H.SalesOrderID = @SalesOrderID', N'@SalesOrderID INT', 56000GO
EXEC sp_executesql N'SELECT SUM(LineTotal) AS LineTotalFROM Sales.SalesOrderHeader HJOIN Sales.SalesOrderDetail D ON D.SalesOrderID = H.SalesOrderIDWHERE H.SalesOrderID = @SalesOrderID', N'@SalesOrderID INT', 56005GO
select stats.execution_count AS exec_count, LEFT([sql].[text], 80) as [plan_text]from sys.dm_exec_cached_plans pouter apply sys.dm_exec_sql_text (p.plan_handle) sqljoin sys.dm_exec_query_stats stats ON stats.plan_handle = p.plan_handleGO
See the difference :D
You can set the parameterization at databse level by :
ALTER DATABASE adventureworks set PARAMETERIZATION SIMPLE.

Awaiting your comments :)

Cannot open user default database. Login failed.

Well , there can be many different scenarios but I am pretty sure those will be simple to drill down ...

In my case the user had a group and there were 6 users in that group
The user created a login for that group in SQL Server and whenever the users wer etrying to login through SSMS they were getting the error below :

Msg 4064, Level 11, State 1, Server ServerName, Line 1
Cannot open user default database. Login failed.

out of 6 users only 4 were having this problem .So we changed the default database for the login to master.It still gives the same error.Then we then created a new login for one of the users from that group seperately and its default database was set to master .we logged in using that login successfully .that ws strange.We then deleted that login and again tried to login again via SSMS using same login thta we deleted .Ideally it should have given us an error something like "login failed for the user ....18456" but It was still giving us the same error .
Cannot open user default database. Login failed.
Login failed for user 'XXXX\YYYY'. (Microsoft SQL Server, Error: 4064)

This made us feel that this user should also a part of other groups and those groups must be pointing to some defult database that does not exist or in a condition described in KB307864 (http://support.microsoft.com/kb/307864)

We then ran this query in master
select name,dbname from syslogins where dbname not in (select dbname from sysdatabases)
And we won the battle :D ...It showed us 5 logins which have a default database that does not exist.So we concluded that all or less users in the current group also exist in all or some of the other groups where the default database does not exist .We then changed the default database for those 5 logins to master and the issue was resolved.

Howzzzat........

What if we want to run sp_msforeachtable for all databases together ?

First ,here are few msforeach commands that you may find useful. :


sp_autostats for each table in a database :
EXEC sp_MSforeachtable @command1="print '?' EXEC sp_autostats '?'"

Reindexing each index in a database :
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"

Index fragmentation for each index in a database :
EXEC sp_MSforeachtable @command1="print '?' DBCC SHOWCONTIG('?')"

Space used by each table in a database :
EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"

Updating statistics in a database :
EXEC sp_MSforeachtable 'UPDATE STATISTICS ? WITH FULLSCAN'

Disabling triggers for a database :
EXEC sp_msforeachtable "ALTER TABLE ? DISABLE TRIGGER all"

finding total number of rows in all the tables in a database :
EXEC sp_msforeachtable 'select count (*) as ''?'' from ?'

to detach all the user databases :
select 'EXEC sp_detach_db @dbname = N'''+RTRIM ( name)+''','''+'true''' from sys.sysdatabases where dbid>4 and name not like 'distribution'


You know the object name, but don't know in which database it is.Run the following query in Text mode :

sp_msforeachdb 'use ?;print "?";select name from sys.sysobjects where name='''''


Now , lets say you need to run them for each database ..but you cannot expect it to run all together for all the databases just like sp_msforeachdb..

Lets try this way :
DECLARE @SQL NVarchar(4000)
SET @SQL = ''
SELECT @SQL = @SQL + 'EXEC ' + NAME + '..sp_MSforeachtable @command1=''DBCC DBREINDEX (''''*'''')'', @replacechar=''*''' + Char(13)
FROM MASTER..Sysdatabases
PRINT @SQL

Here , what we are doing is initialising @sql with nothing .then first time it will get master.So it will put master plus the remaining string in @sql ...ie. 'EXEC ' + NAME + '..sp_MSforeachtable @command1=''DBCC DBREINDEX (''''*'''')'', @replacechar=''*''' + Char(13).
So , in first flow @ will contain
EXEC master..sp_MSforeachtable @command1='DBCC DBREINDEX (''*'')', @replacechar='*'
In the second round it will have the previous string in @sql plus the same string for the next database name.To check this , just remove "@SQL+" stuff and run this script .You will see the string for the last DBID because each time the value in @SQL was overwritten :)..
Hence , this string will keep on increasing till the number of databases count ends ..So , you may have to increase the nvarchar limit if therer are a lot many databases ..

I have also copied few MSxxxxx stuff for your referance:

EXEC sp_MSforeachdb @command1="print '?' DBCC CHECKDB ('?')"
EXEC sp_MShelpcolumns 'Production.products'
EXEC sp_MSindexspace 'Production.products'
EXEC sp_MStablespace 'Production.products'

Hope this helps :D

Saturday, February 9, 2008

SQL Server 2005 set up takes a very long time to complete

Issue :
SQL Server 2005 set up takes a very long time to complete
Environment :
Database :SQL Server Enterprise Edition 2005 EN
OS : Windows server 2003

Root Cause :
Explanation : Why is it taking too much time to install SQL Server 2005
===============================================
SQL Server 2005 Setup needs to configure security settings. When the primary domain has many external trust relationships with other domains or many lookups are performed at the same time, the time that is required to look up domain group names may increase significantly. Therefore, the time that is required to install SQL Server 2005 may increase. This is because by default, in the Microsoft Windows Server 2003 family and in the Microsoft Windows 2000 Server family, when the LookupAccountName function or the LsaLookupNames function resolves isolated names to security identifiers (SIDs), a remote procedure call (RPC) is made to domain controllers on external trusted domains. An isolated name is an ambiguous, non-domain-qualified user account.

Solutions :

Solution 1 :

Temporarily disable the network on the computer on which the SQL Server 2005 Setup program is running. To do this, either disconnect the network cable or type the following command at a command prompt: ipconfig /release *Con*. If you use ipconfig utility make sure to run ipconfig /renew *Con* after installation is done.
Note :This workaround will not succeed when you are installing a SQL Server failover cluster.

Solution 2 :

Follow the steps that are listed in the following Microsoft Knowledge base article to restrict the lookup of isolated names in external trusted domains:
818024 How to restrict the lookup of isolated names in external trusted domains by using the LsaLookupRestrictIsolatedNameLevel registry entry

Solution 3 :

Apply hotfix as per KB 910070
These are the exact steps that you need to perform.

Step 1 : download the zip file to some location and extract it .
Step 2 : Now you will be having the files mentioned below
• AS2005-KB919357-x86-ENU.exe
• SQL2005-KB919357-x86-ENU.exe
• DTS2005-KB919357-x86-ENU.exe
• NS2005-KB919357-x86-ENU.exe
• RS2005-KB919357-x86-ENU.exe
• SQLTools2005-KB919357-x86-ENU.exe
• SQLSupport2005-KB919357-x86-ENU.exe
Step 3 : At a command prompt, run these .exe files by specifying the /X switch to extract these .exe files into different folders.

• AS2005-KB919357-x86-ENU.exe /X:C:\QFE\AS
• SQL2005-KB919357-x86-ENU.exe /X:C:\QFE\SQL
• DTS2005-KB919357-x86-ENU.exe /X:C:\QFE\IS
• NS2005-KB919357-x86-ENU.ex /X:C:\QFE\NS
• RS2005-KB919357-x86-ENU.exe /X:C:\QFE\RS
• SQLTools2005-KB919357-x86-ENU.exe /X:C:\QFE\Tools
• SQLSupport2005-KB919357-x86-ENU.exe /X:C:\QFE\SqlSupport
Step 4 : After you extract these .exe files, the .msp files appear in the following folders: • C:\QFE\AS\hotfixas\files\sqlrun_as.msp

• C:\QFE\SQL\hotfixsql\files\sqlrun_sql.msp
• C:\QFE\IS\hotfixdts\files\sqlrun_dts.msp
• C:\QFE\NS\hotfixns\files\sqlrun_ns.msp
• C:\QFE\RS\hotfixrs\files\sqlrun_rs.msp
• C:\QFE\Tools\hotfixtools\files\sqlrun_tools.msp
• C:\QFE\SqlSupport\msp\sqlsupport.msp

Step 5 : Install SQL Server 2005 by running a command that resembles the following command:

\Servers\setup.exe SQLSUPPORTPATCH="C:\QFE\SqlSupport\msp\SqlSupport.msp" HOTFIXPATCH="C:\QFE\AS\hotfixas\files\sqlrun_as.msp;C:\QFE\SQL\hotfixsql\files\sqlrun_sql.msp;C:\QFE\IS\hotfixdts\files\sqlrun_dts.msp;C:\QFE\NS\hotfixns\files\sqlrun_ns.msp;C:\QFE\RS\hotfixrs\files\sqlrun_rs.msp;C:\QFE\Tools\hotfixtools\files\sqlrun_tools.msp"

This is the example that I followed :
C:\Enterprise\servers\setup.exe SQLSUPPORTPATCH="C:\QFE\SqlSupport\msp\SqlSupport.msp" HOTFIXPATCH="C:\QFE\AS\hotfixas\files\sqlrun_as.msp;C:\QFE\SQL\hotfixsql\files\sqlrun_sql.msp;C:\QFE\IS\hotfixdts\files\sqlrun_dts.msp;C:\QFE\NS\hotfixns\files\sqlrun_ns.msp;C:\QFE\RS\hotfixrs\files\sqlrun_rs.msp;C:\QFE\Tools\hotfixtools\files\sqlrun_tools.msp"

Useful Links :
KB 910070 and KB 818024

What to Do if you have screwed up your SQL Server 2005 Setup

For Stand alone machine :
--------------------------
Suppose you failed SQL Server 2005 setup , tried again ..failed ..did some changes , deleted few folders etc ....etc ......and finally messed up .......now what :)........

before blowing up the machine try this ..
open a notepad and copy this content :

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client]

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo]

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\DB-Lib]
"AutoAnsiToOem"="ON"
"UseIntlSettings"="ON"

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SNI9.0]

"ProtocolsSupported"=hex(7):73,00,6d,00,00,00,74,00,63,00,70,00,00,00,6e,00,70,
00,00,00,76,00,69,00,61,00,00,00,00,00

"ProtocolOrder"=hex(7):73,00,6d,00,00,00,74,00,63,00,70,00,00,00,6e,00,70,00,
00,00,00,00


[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SNI9.0\GeneralFlags]
"NumberOfFlags"=dword:00000002


[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SNI9.0\GeneralFlags\Flag1]


"Label"="Force protocol encryption"
"Value"=dword:00000000


[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SNI9.0\GeneralFlags\Flag2]


"Label"="Trust Server Certificate"
"Value"=dword:00000000


[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SNI9.0\LastConnect]

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SNI9.0\np]
"DLLname"="SQLNCLI"
"NumberOfFlags"=dword:00000000
"NumberOfProperties"=dword:00000001
"ProtocolName"="Named Pipes"


[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SNI9.0\np\Property1]
"Name"="Default Pipe"
"Value"="sql\\query"

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SNI9.0\sm]
"DLLname"="SQLNCLI"
"NumberOfFlags"=dword:00000000
"NumberOfProperties"=dword:00000000
"ProtocolName"="Shared Memory"

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SNI9.0\tcp]
"DLLname"="SQLNCLI"
"NumberOfFlags"=dword:00000000
"NumberOfProperties"=dword:00000003
"ProtocolName"="TCP/IP"


[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SNI9.0\tcp\Property1]
"Name"="Default Port"
"Value"=dword:00000599


[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SNI9.0\tcp\Property2]
"Name"="KEEPALIVE (in milliseconds)"
"Value"=dword:00007530


[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SNI9.0\tcp\Property3]
"Name"="KEEPALIVEINTERVAL (in milliseconds)"
"Value"=dword:000003e8

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SNI9.0\VIA]
"DLLname"="SQLNCLI"
"NumberOfFlags"=dword:00000000
"NumberOfProperties"=dword:00000002
"ProtocolName"="VIA"


[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SNI9.0\VIA\Property1]
"Name"="Default Server Port"
"Value"="0:1433"


[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SNI9.0\VIA\Property2]
"Name"="Default Client NIC"
"Value"="0"

Now save this file as .reg >> double click this file and import it ......
start the setup .......and cross your fingures .....
I tried this on STD edition ........
Actually this is the complete healthy registry key set needed for a successful setup ...
I have seen 50% problems get solved ..
All the best to you ......
Try at your own risk and take a complete registry backup .....

It is possible to delete the data/log files in SQL Express Edition even if the Instance is UP

Yes , that is true but thankfully only in SQL Express Edition :)

Research :This behavior is by design in SQL Express. Until the user database is used explicitly by user, sql will not recover/open that database so at the time of SQL startup anyone can delete/move the datafiles. Once we connect to database, following entry gets logged into the SQL errorlog: 2007-06-07 19:16:09.59 spid51 Starting up database 'Test2'. This behavior using FILEHANDLE.EXE.So the risk of file delete will be there till the application connects to SQL (or actually until the filehandle is acquired by the SQL server service)
Resolution / Workaround : The easiest workaround of this problem would be creating a startup stored procedure which will query any table from the user database. i.e.

1) Suppose we have a user database named 'TEST' in Express instance, create a stored procedure in master database which will query one row from this database:
use master
go
create procedure start
as
select top 1 id from TEST.dbo.testTable

2) Set this SP as startup stored procedure for this instance:
sp_procoption 'dbo.start','startup' ,'true'

Now on every startup, SQL will query this database table which will in turn open this database automatically.

Enjoi :)

Shrinking a Log file

What if your database is in Full recovery mode and the T-logs are huge :D.....

This indicates that either you have not taken the backups since long or the backups are failing .In Full recovery the T-Logs keeps on growing untill you take the backups .This is not so in simple recovery mode ...

Now , if you try to shrink the database or the T-log file , it will not .Because there may be uncommitted or not backed up transactions at the bottom of the virtual log file ..

To check this run DBCC Loginfo command .You will see the last value in the status column as 2 ..
to make it 0 :

1) take full backup ...
2) change the database recovery model to simple

and then shrink the logfile DBCC SHRINKFILE (msdblog);

if you do not want to take any of the steps above try this :
repeatedly run the following script until
DBCC logingo show the last entry as having a status of '0' ('2' should be move to one of the first entries)

CREATE TABLE DummyTrans (DummyColumn char (8000) not null)
DECLARE @Counter int
SELECT @Counter = 1
WHILE @Counter < 1000
BEGIN
INSERT DummyTrans VALUES ('XYZ')
SELECT @Counter = @Counter + 1
END
DROP TABLE DummyTrans


USE UserDB
GO
DBCC SHRINKFILE (logfile name )
GO

Hope this helps ......

Avoid using Trace flags in Startup parameters

Symptoms :
Client did an inplace upgrade from 2000 to 2005 and Server CPU utilization showed quite high during idle times. The CPU utilization is 25% to 29% most of time even there is no user connections to the server.also the SSMS seems to be very slow in response ..

I checked every thing but still could not find any clue ....
I had 2 questions :

1) Why SQL Server is consuming 28-30% CPU when its not doing anything
2) Why everything seems drastically slow at 28% CPU when other instance is pretty fast at 57% CPU .....

I finally checked the Error logs and found that 3 trace flags were enabled in the startup parameter viz. T818,-T815 and -T8809

.....

Error: 26054, Severity: 16, State: 1

Imagine if your SQL Server resource is not coming up and you are getting something like this in the errorlog:

2007-11-29 00:10:03.00 Server Error: 26054, Severity: 16, State: 1.
2007-11-29 00:10:03.00 Server Could not find any IP address that this SQL Server instance depends upon. Make sure that the cluster service is running, that the dependency relationship between SQL Server and Network Name resources is correct, and that the IP addresses on which this SQL Server instance depends are available. Error code: 0x103.
2007-11-29 00:10:03.00 Server Error: 17182, Severity: 16, State: 1.
2007-11-29 00:10:03.00 Server TDSSNIClient initialization failed with error 0x103, status code 0xa.
2007-11-29 00:10:03.00 Server Error: 17182, Severity: 16, State: 1.
2007-11-29 00:10:03.00 Server TDSSNIClient initialization failed with error 0x103, status code 0x1.
2007-11-29 00:10:03.00 Server Error: 17826, Severity: 18, State: 3.
2007-11-29 00:10:03.00 Server Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.
2007-11-29 00:10:03.00 Server Error: 17120, Severity: 16, State: 1.
2007-11-29 00:10:03.00 Server SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.


all these errors are very different .But we need to see the first error ...
lets check the following registry key for all the instances we have on the cluster :
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL.1\Cluster(MSSQLSERVER)

Check the virtual server name ......it has to be different for all the instances ........right ..
In my case we had 2 instances and both had same virtual server names ( dont ask why ) not sure what the other person had done in the past .

Change the name of the virtual server on all nodes as needed and run the checkpoint on registry or

Restoring System databases when LDF files have gone for a toss ..

Problem -

--Due to some Hardware problem SAN drive having LDF files for all of the system database lost.

--Now you have recreated a new drive with same drive letter but contents are not there.

--You have database backup for all the system databases and other user databases.

--You have MDF file for system database as well.

Solution :

1) first use rebuildm utility .If that fails then ,
2) Restore all SYSTEM databases on a test instance of SQL with “new” as prefix.( remember you have backups :D )

--Detach those databases from the test instance.

--On original instance of SQL rename all the previous system MDF files with “old” as prefix.

--Copied all the MDF & LDF files from test instance to this actual instance folder and rename data file named to actual one (like master.mdf, model.mdf, msdbdata.mdf).and log files as well .

--Gave proper permissions to all of the newly copied files if needed .

--Start SQL server and it starts successfully.

--Attach all user database and everything will work fine.

Hope it helps :)

DTExec: The package execution returned DTSER_FAILURE (1)

Is your M-Plan failing ......and you are getting this error :
Message
Executed as user: TMMCADM\ClusterSQL. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started:
11:35:48 AM Progress: 2008-01-30 11:35:49.11 Source: {3764B470-DBB1-4A59-978D-E2CE331A1AF2} Executing query "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp".: 100%
complete End Progress DTExec: The package execution returned DTSER_FAILURE (1). Started: 11:35:48 AM Finished: 11:35:49 AM Elapsed: 1.218 seconds. The package execution failed. The
step failed.


Check the sp_configure values and see if "allow updates" is set to 1 by mistake ..
reset it to 0 and you are done ......

Restoring Database on a network using UNC path

Have you ever tried to keep your database files away from the actual server ...
Say , your SQL Server is on Server A and datafiles on Server B ....is it possible ..

Lets try :)

RESTORE DATABASE [TestCorruption_Load]
FROM DISK = N'\\abhay\Backup\TestCorruption_Load.bak'
WITH STATS = 10, RECOVERY , REPLACE ,
MOVE N'TestCorruption_Load' TO N'\\abhay\Backup\abc.mdf',
MOVE N'TestCorruption_Load_log' TO N'\\abhay\Backup\abc.ldf'

Results :

Msg 5110, Level 16, State 2, Line 1
The file "\\abhay\Backup\abc.mdf" is on a network path that is not supported for database files.
Msg 3156, Level 16, State 3, Line 1
File 'TestCorruption_Load' cannot be restored to '\\abhay\Backup\abc.mdf'. Use WITH MOVE to identify a valid location for the file.
Msg 5110, Level 16, State 2, Line 1
The file "\\abhay\Backup\abc.ldf" is on a network path that is not supported for database files.
Msg 3156, Level 16, State 3, Line 1
File 'TestCorruption_Load_log' cannot be restored to '\\abhay\Backup\abc.ldf'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 1
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Yes , normally its not allowed and Microsoft does not support this ...

Now , enable trace flag 1807 (http://support.microsoft.com/kb/304261)
dbcc traceon (3604,1807 , -1)

and here we go :


RESTORE DATABASE [TestCorruption_Load] FROM DISK = N'\\abhay\Backup\TestCorruption_Load.bak' WITH STATS = 10, RECOVERY , REPLACE , MOVE N'TestCorruption_Load' TO N'\\abhay\Backup\abc.mdf', MOVE N'TestCorruption_Load_log' TO N'\\abhay\Backup\abc.ldf'

Processed 912 pages for database 'TestCorruption_Load', file 'TestCorruption_Load' on file 1.
Processed 2 pages for database 'TestCorruption_Load', file 'TestCorruption_Load_log' on file 1.
RESTORE DATABASE successfully processed 914 pages in 1.264 seconds (5.917 MB/sec).

one interesting fact that I have noticed is : the performance is much faster here as compared to non UNC restore .....try it on the test machine ...

But again , this is risky and not supported yet ....
Hoping this will be suppported some day....

ERROR_INSTALL_PACKAGE_OPEN_FAILED winerror.h

Error:
for decimal 1619 / hex 0x653 ecFavMoveOffline ec.h
ERROR_INSTALL_PACKAGE_OPEN_FAILED winerror.h
This installation package could not be opened. Verify that the package exists and that you can access it, or contact
the application vendor to verify that this is a valid Windows Installer package.
SQL_1619_severity_21 sql_err Could not open tempdb. Cannot continue.
as an HRESULT: Severity: SUCCESS (0), FACILITY_NULL (0x0), Code 0x653 for decimal 1619 / hex 0x653
ERROR_INSTALL_PACKAGE_OPEN_FAILED winerror.h
This installation package could not be opened.Verify that the package exists and that you can access it, or contact
the application vendor to verify that this is a valid Windows Installer package.


You will be getting this error when the setup will blow up in the very begening .

Root cause : Windows installer needs the SYSTEM user added to the folder where the SQL Server setup files are located ..by default its added to every folder .But if someone plays around with OS user and groups there is a possibility that the "by default setting" goes away ..

Solution : add SYSTEM to the SQL Server setup folder and give full permission ....

SQL Server memory and CLR assemblies

I will keep this short .First of all CLR and JVM are built upon same concepts :) ..merging of the CLR with SQL Server 2005 gives SQL Server the ability to create database objects using modern object-oriented languages like VB.NET and C#because CLR Objects support complex logic and have better computation capabilities.Thats the reason its use is increasing day by day ..

many a times i have seen cases where people say that they have memory pressure due to CLR objects ..we always feel that ( include me as well ) CLR objects / assemblies will only consume MemToLeave ..but it also consumes the buffer pool..butttt most SQL CLR memory allocations occur outside of the SQL Buffer Pool in MemToLeave area (384 MB by default)..

[ Mem2Leave= 255 worker threads x .5MB +256 MBStack size<--called as -g switch]

Now , There are two types of memory pressures
1) Physical memory pressure based on the amount of available system memory
2) Virtual Address Space (VAS : 2 gigs by default on 32 bit architecture)memory pressure based on the number of available virtual addresses.

Query sys.dm_os_memory_clerks dmv :
select single_pages_kb + multi_pages_kb + virtual_memory_committed_kb from sys.dm_os_memory_clerks where type = 'MEMORYCLERK_SQLCLR'

Here single_pages_kb is buffer pool and multi_pages_kb is MemToLeave area..the same and better output can be seen in DBCC Memorystatus as well.

Now,SQL CLR can’t use so much memory that it causes external physical memory pressure (Don't ask why :D).

So, SQL CLR will not be alowed to use more than 256 MB by default..

Now , if you are getting errors like :

Error: 701 , Severity: 17, State: 123.
There is insufficient system memory to run this query.
or
failed to reserve a contiguous space of 65536 K

first thing I would suggest is concentrate on the CLR Assemblies and see if we can optimize it
Other stuff we can do is increasing Mem2Leave area by using -g384 or -g512 in the startup parameters (if we see that the buffer pool is not heavily used)...further we can check if there is no harm using /3gb switch in the boot.ini file ....keep server activity confined to SQL Server , keep you database finely tuned by using more SPs , updating stats , defragmenting disks , rebuilding indexes .....

if nothing is helping you move to 64 bit machines :D.......thts the last option....
Never , use CLR based assemblies on a Virtual server ....
Your comments are welcome ...