Sql Server Perfmon counters for Memory

Here there are few sql server perfmon counters which will tell you about memory bottleneck for your sql server instance.Monitor these counters and compare your value with below given values.

1- Buffer cache hit ratio--

-Indicates how often sql server can get data from the buffer rather than disk.
-Buffer cache hit ratio > 90% for OLAP
-Buffer cache hit ratio > 95% for OLTP

2-Free list stalls/sec--

-The frequeny that requests for database buffer pages are suspended because there's no buffer available.
-Free list stalls/sec < 2
-if value is high that means memory shoud be increase.

3-Free pages--

-The total no of 8k data pages on all free lists
-Free pages > 640

4-Lazy writes/sec--

-The no of times per second that lazy writer moves dirty pages from buffer to disk to free buffer space.
-Lazy writes/sec < 20
-greater value will indicate memory bottleneck.


5-Page Life Expectancy--

-No of seconds a data page stays in the buffer.
-Page Life Expectancy > 300,otherwise memory pressure is at play.

6-Page Lookups/Sec--

-No of requests to find a page in the buffer.
-Page lookups/sec)/(Batch request/sec)<100

7-Page Reads/sec and Page writes/sec--

-no of physical db page reads and writes issued,respectly
-Value should be < 90

HOWTO: Change Microsoft SQL Server Authentication mode from windows to Mixed Mode

There are two method to change the authentication mode from windows to mixed.

1-GUI
2-Registry

1-GUI Method-

Connect to Instance then go to Instance property.Choose security option from left side,here you can change from windows to Mixed mode.after changing restart the sql server services.

2-Registry Method-

For SQL Server 2005 Express Edition,You need to go it manually. The first step is to change the login-mode.

Open registry editor (launch application %WINDIR%\regedit.exe) and go to HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer in the tree on the left.

On the right, look for an entry named LoginMode. The default value, when installed is 1. Update it to 2. The next step is to restart the service.

Launch your Service Manager (Start -> Run -> Type services.msc) and look for a service named MSSQL Server (SQLEXPRESS). Restart the service.

How to move a table to a different filegroup

We can change a table from one filegroup to any other filegroup through droping and recreting the clustered indexes.below is given ALTER cmnds which will move your whole table from filegroup1 to filegroup2.

Cmnds are-

ALTER TABLE TAB1 DROP CONSTRAINT PK_TAB1 WITH (MOVE TO TEST_DATA_2)
GO
ALTER TABLE TAB1 ADD CONSTRAINT PK_TAB1 PRIMARY KEY(TAB1_ID)
GO

where TAB1 is table name PK_TAB1 is clustered index key and TEST_DATA_2 is target filegroup.

Point in Time Recovery

sometimes detrimental command will probably be issued against one of your databases and you will need to recover the lost data. There are several actions that you might be able to take to recover the lost data, but what option makes the most sense. One option that SQL Server offers is the ability to do point in time restores of your data in order to restore your database back to the point right before that detrimental command was issued.

Point-in-Time Restore:-

1-This topic is relevant only for databases that are using full or bulk-logged recovery mode. Under the bulk-logged recovery model, if a log backup contains bulk-logged changes, point-in-time recovery is not possible to a point within that backup. The database must be recovered to the end of the transaction log backup.
2-Restore the last full database backup and, if any, the last differential database backup without recovering the database (RESTORE DATABASE database_name FROM backup_device WITH NORECOVERY).
3-Apply each transaction log backup in the same sequence in which they were created, specifying the time at which you intend to stop restoring log (RESTORE DATABASE database_name FROM WITH STOPAT=time, RECOVERY).
4-The basic syntax is:

RESTORE LOG database_name FROM WITH STOPAT =time, RECOVERY…


Cmnds are:

-- Restore the full database backup.
RESTORE DATABASE AdventureWorks
FROM disk='backup file location'
WITH NORECOVERY;
GO
RESTORE LOG AdventureWorks
FROM disk='backup file location'
WITH RECOVERY, STOPAT = 'Apr 15, 2005 12:00 AM';
GO

Not able to open DTS package in sql server 2008 or sql server 2005.

When you get below error during accessing the DTS packages :

SQL Server 2000 DTS Designer components are required to edit DTS packages. Install the special Web download, “SQL Server 2000 DTS Designer Components” to use this feature. (Microsoft.SqlServer.DtsObjectExplorerUI)

Then to resolve this issue follow the below processes.


1-Install Sql Server 2000 DTS Designer Components
Download from here. This is the most current version as of the writing of this article.
2-Install Sql Server 2005 backward compatibility components
Download from here. This is the most current version as of the writing of this article.
This is available with the installation media for SQL Server 2008.
3-Verify your path environment variable. The SQL 2000 path should be placed in the string prior to the SQL 2008 variable. As a sample, this is what mine looks like.
%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem;%SYSTEMROOT%\System32\WindowsPowerShell\v1.0\;C:\Program Files\Microsoft SQL Server\80\Tools\Binn\;C:\Program Files\Microsoft SQL Server\90\Tools\binn\;C:\Program Files\Microsoft SQL Server\90\DTS\Binn\;C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\;C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\;C:\Program Files\Microsoft SQL Server\100\DTS\Binn\;C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\;C:\Program Files\Microsoft SQL Server\100\Tools\Binn\;C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\
*You can access the environment variables in Windows 7 by: Right Click My Computer -> Properties -> Click Advanced System Settings -> Click Environment Variables -> Scroll to “Path” -> Click “Path” and then click Edit…
4-SSMS – Manually Copy Files
DLL Files to copy
semsfc.dll, sqlgui.dll, sqlsvc.dll
Source
%Program Files%\Microsoft SQL Server\80\Tools\Binn\
Destination
%Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\
%Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\%lang_id%\
RLL Files to copy
semsfc.rll, sqlgui.rll, sqlsvc.rll
Source
%Program Files%\Microsoft SQL Server\80\Tools\Binn\Resources\%lang_id%\
Destination
%Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Resources\%lang_id%\
5-BIDS Manually Copy Files
DLL Files to copy
semsfc.dll, sqlgui.dll, sqlsvc.dll
Source
%Program Files%\Microsoft SQL Server\80\Tools\Binn\
Destination
%Program Files%\Microsoft Visual Studio 9.0\Common7\IDE\
RLL Files to copy
semsfc.rll, sqlgui.rll, sqlsvc.rll
Source
%Program Files%\Microsoft SQL Server\80\Tools\Binn\Resources\%lang_id%\
Destination
%Program Files%\Microsoft Visual Studio 9.0\Common7\IDE\Resources\%lang_id%\
I had to create the Resources Subdirectory
%Program Files%\Microsoft Visual Studio 9.0\Common7\IDE\%lang_id%\
The last step was not applicable in my case since the file version already matched
stardds.dll File version: 2000.80.2151.0