Some of the important performance counters related to memory in SQL Server are

SQL Server Cache Hit Ratios: A consistent value below 90% indicates that more physical memory is needed on the server.
Memory Object: Available Bytes: On a server dedicated to SQL Server, SQL Server attempts to maintain from 4-10MB of free physical memory. The remaining physical RAM is used by the operating system and SQL Server.
Memory Object: Pages/Sec: Measures the number of pages per second that are paged out from RAM to disk. Higher the value, higher will be I/O activities and will result in decrease in performance. If you have only SQL server application running on the server then in most cases this value should be near zero. However you don’t see much performance degradation until it is 20, when SQL Server is not the only application. Above 20, it is an indication to have more RAM on the server.
SQLServer:Memory Manager: Total Server Memory (KB):Tells you how much memory your SQL Server is currently using.
SQLServer:Memory Manager: Target Server Memory (KB):Tells you howmuch memory SQL Server would like to use to operate efficiently. If the SQLServer:Memory Manager: Total Server Memory (KB) counter is more or equal than the SQLServer:Memory Manager: Target Server Memory (KB) counter, this indicates that SQL Server may be under memory pressure and could use access to more physical memory.
SQL Server Buffer Manager Object: Cache Size (pages): Multiply this value by 8K to get the value of physical RAM devoted to SQL Server data cache. If this value is less than what you expect than SQL Server is starving on the memory and is not utilizing the available physical RAM.
Process: Working Set: shows the amount of memory used by process. If this number is consistently below Min Server Memory and Max Server Memory then SQL Server is configured to use too much memory.

When the system is running out of memory, it will have higher paging and disk I/O. You can measure the Memory: Page Faults/sec counter to make sure that the disk activity is not caused by paging. You can also monitor SQL Server:Buffer Manager:Page reads/sec and SQL Server:Buffer Manager:Page writes/sec performance counters. Check more at: http://msdn2.microsoft.com/en-us/library/ms175903.aspx.
There is a relationship between SQL Server disk I/O and CPU usage. Check the article Monitoring CPU Usage - http://msdn2.microsoft.com/en-us/library/ms178072.aspx for more details.
Following are other very useful articles for troubleshooting SQL Server performance.
SQL Server Memory Monitoring - http://msdn2.microsoft.com/en-us/library/ms176018.aspx. This article also contains the list of performance counters for monitoring SQL Server memory. The Process: Working Set counter shows the amount of memory that is used by a process.

Killed Last 10 min Inactive Sessions of a Database

DECLARE @DatabaseName nvarchar(50)

SET @DatabaseName = N'DATABASE NAME'

--SET @DatabaseName = DB_NAME()

DECLARE @SQL varchar(max)

SET @SQL = ''

SELECT @SQL = @SQL + 'Kill ' + Convert(varchar, SPId) + ';'

FROM MASTER..SysProcesses

WHERE DBId = DB_ID(@DatabaseName) AND SPId <> @@SPId

AND DATEDIFF(mi,last_batch,GETDATE())>=10

-- SELECT @SQL

EXEC(@SQL)

Findout the Fragmented Table and Index Name

Step-1.
CREATE FUNCTION dbo.index_name (@object_id int, @index_id int)
RETURNS sysname
AS
BEGIN
  RETURN(SELECT name FROM sys.indexes WHERE object_id = @object_id and index_id = @index_id)
END;

Step-2.

SELECT 
 OBJECT_NAME(object_id) AS tblName
,dbo.index_name(object_id, index_id) AS ixName
,avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL)
WHERE avg_fragmentation_in_percent > 20
AND index_type_desc IN('CLUSTERED INDEX', 'NONCLUSTERED INDEX')

Step-3:
To fix execute:

dbcc indexdefrag (ADVANTUREWORKS,'sales.storecontact',AK_StoreContact_rowguid)

Get configuration values for instance

SELECT name, value, value_in_use, [description]

FROM sys.configurations

ORDER BY name OPTION (RECOMPILE);

Findout SQL is listening on which port

USE master
GO
xp_readerrorlog 0, 1, N'Server is listening on'

SQL 2005
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.\MSSQLServer\
SuperSocketNetLib\TCP\

SQL 2008
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.\MSSQLServer\
SuperSocketNetLib\TCP\

Look at currently executing requests, status and wait type

SELECT r.session_id, r.[status], r.wait_type, r.scheduler_id,

SUBSTRING(qt.[text],r.statement_start_offset/2,

(CASE WHEN r.statement_end_offset = -1

THEN LEN(CONVERT(nvarchar(max), qt.[text])) * 2

ELSE r.statement_end_offset

END - r.statement_start_offset)/2) AS [statement_executing],

DB_NAME(qt.[dbid]) AS [DatabaseName],

OBJECT_NAME(qt.objectid) AS [ObjectName],

r.cpu_time, r.total_elapsed_time, r.reads, r.writes,

r.logical_reads, r.plan_handle

FROM sys.dm_exec_requests AS r

CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS qt

WHERE r.session_id > 50

ORDER BY r.scheduler_id, r.[status], r.session_id;

Active Lock Manager Resources for Current Database

SELECT request_session_id, DB_NAME(resource_database_id) AS [Database],

resource_type, resource_subtype, request_type, request_mode,

resource_description, request_mode, request_owner_type

FROM sys.dm_tran_locks

WHERE request_session_id > 50

AND resource_database_id = DB_ID()

AND request_session_id <> @@SPID

ORDER BY request_session_id;

Findout blockings

SELECT tl.resource_type, tl.resource_database_id, tl.resource_associated_entity_id, tl.request_mode, tl.request_session_id, wt.blocking_session_id, wt.wait_type, wt.wait_duration_ms
FROM sys.dm_tran_locks as tl
INNER JOIN sys.dm_os_waiting_tasks as wt
ON tl.lock_owner_address = wt.resource_address
ORDER BY wait_duration_ms DESC;

Get a count of SQL connections by IP address

SELECT ec.client_net_address, es.[program_name],

es.[host_name], es.login_name,

COUNT(ec.session_id) AS [connection count]

FROM sys.dm_exec_sessions AS es

INNER JOIN sys.dm_exec_connections AS ec

ON es.session_id = ec.session_id

GROUP BY ec.client_net_address, es.[program_name], es.[host_name], es.login_name

ORDER BY ec.client_net_address, es.[program_name];

Physical CPU Count

SELECT cpu_count AS [Logical CPU Count], hyperthread_ratio AS [Hyperthread Ratio],

cpu_count/hyperthread_ratio AS [Physical CPU Count],

physical_memory_in_bytes/1048576 AS [Physical Memory (MB)],

sqlserver_start_time --, affinity_type_desc -- (affinity_type_desc is only in 2008 R2)

FROM sys.dm_os_sys_info OPTION (RECOMPILE);

Get logins that are connected and How many sessions they have

SELECT login_name, COUNT(session_id) AS [session_count]
FROM sys.dm_exec_sessions WITH (NOLOCK)
WHERE session_id > 50 -- filter out system SPIDs
GROUP BY login_name
ORDER BY COUNT(session_id) DESC OPTION (RECOMPILE);

Get processor description from Windows Registry

EXEC xp_instance_regread
'HKEY_LOCAL_MACHINE',
'HARDWARE\DESCRIPTION\System\CentralProcessor\0',
'ProcessorNameString';

Suggested Max Memory Settings for SQL Server 2005/2008

It is very important to make sure you set the Max Server memory setting for SQL Server 2005/2008 to something besides the default setting (which allows SQL Server to use as much memory as it wants, subject to signals from the operating system that it is under memory pressure). This is especially important with larger, busier systems that may be under memory pressure.

This setting controls how much memory can be used by the SQL Server Buffer Pool. If you don’t set an upper limit for this value, other parts of SQL Server, and the operating system can be starved for memory, which can cause instability and performance problems. It is even more important to set this correctly if you have “Lock Pages in Memory” enabled for the SQL Server service account (which I always do for x64 systems with more than 4GB of memory).

These settings are for x64, on a dedicated database server, only running the DB engine, (which is the ideal situation).

Physical RAM MaxServerMem Setting
2GB 1500
4GB 3200
6GB 4800
8GB 6400
12GB 10000
16GB 13500
24GB 21500
32GB 29000
48GB 44000
64GB 60000
72GB 68000
96GB 92000
128GB 124000

If you are running other SQL Server components, such as SSIS or Full Text Search, you will want to allocate less memory for the SQL Server Buffer Pool. You also want to pay close attention to how much memory is still available in Task Manager. This is how much RAM should be available in Task Manager while you are under load (on Windows Server 2003):

Physical RAM                   Target Avail RAM in Task Manager
< 4GB                                     512MB – 1GB
4-32GB                                  1GB – 2GB
32-128GB                              2GB – 4GB
> 128GB                                > 4GB

You can use T-SQL to set your MaxServerMemory setting. The sample below sets it to 3500, which is the equivalent of 3.5GB. This setting is dynamic in SQL Server 2005/2008, which means that you can change it and it goes into effect immediately, without restarting SQL Server.

– Turn on advanced options
EXEC sp_configure‘Show Advanced Options’,1;
GO
RECONFIGURE;
GO

– Set max server memory = 3500MB for the server
EXEC sp_configure‘max server memory (MB)’,3500;
GO
RECONFIGURE;
GO

-- See what the current values are

EXEC sp_configure;

Get CPU Utilization History for last 256 minutes

-- This version works with SQL Server 2008 and SQL Server 2008 R2 only

DECLARE @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks)FROM sys.dm_os_sys_info);

SELECT TOP(256) SQLProcessUtilization AS [SQL Server Process CPU Utilization],

               SystemIdle AS [System Idle Process],

               100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU Utilization],

               DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time]

FROM (

        SELECT record.value('(./Record/@id)[1]', 'int') AS record_id,

                  record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int')

                  AS [SystemIdle],

                  record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]',

                  'int')

                  AS [SQLProcessUtilization], [timestamp]

        FROM (

                  SELECT [timestamp], CONVERT(xml, record) AS [record]

                  FROM sys.dm_os_ring_buffers WITH (NOLOCK)

                  WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'

                  AND record LIKE N'%%') AS x

        ) AS y

ORDER BY record_id DESC OPTION (RECOMPILE);

-- Get CPU Utilization History (SQL 2005 Only)

DECLARE @ts_now bigint; 

SET @ts_now = (SELECT cpu_ticks / CONVERT(float, cpu_ticks_in_ms) FROM sys.dm_os_sys_info); 

SELECT TOP(256) SQLProcessUtilization AS [SQL Server Process CPU Utilization], 

               SystemIdle AS [System Idle Process], 

               100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU Utilization], 

               DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time] 

FROM ( 

          SELECT record.value('(./Record/@id)[1]', 'int') AS record_id, 

                       record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') 

                       AS [SystemIdle], 

                       record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 

                       'int') 

                       AS [SQLProcessUtilization], [timestamp] 

          FROM ( 

                       SELECT [timestamp], CONVERT(xml, record) AS [record] 

                       FROM sys.dm_os_ring_buffers 

                       WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' 

                       AND record LIKE '%%') AS x 

          ) AS y 

ORDER BY record_id DESC OPTION (RECOMPILE);

When were Statistics last updated on all indexes

SELECT o.name, i.name AS [Index Name], 

       STATS_DATE(i.[object_id], i.index_id) AS [Statistics Date],

       s.auto_created, s.no_recompute, s.user_created

FROM sys.objects AS o WITH (NOLOCK)

INNER JOIN sys.indexes AS i WITH (NOLOCK)

ON o.[object_id] = i.[object_id]

INNER JOIN sys.stats AS s WITH (NOLOCK)

ON i.[object_id] = s.[object_id]

AND i.index_id = s.stats_id

WHERE o.[type] = 'U'

ORDER BY STATS_DATE(i.[object_id], i.index_id) ASC OPTION (RECOMPILE);     

Get CPU Utilization by Database

WITH DB_CPU_Stats
AS
(SELECT DatabaseID, DB_Name(DatabaseID) AS [DatabaseName], SUM(total_worker_time) AS [CPU_Time_Ms]
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY (SELECT CONVERT(int, value) AS [DatabaseID]
              FROM sys.dm_exec_plan_attributes(qs.plan_handle)
              WHERE attribute = N'dbid') AS F_DB
GROUP BY DatabaseID)
SELECT ROW_NUMBER() OVER(ORDER BY [CPU_Time_Ms] DESC) AS [row_num],
       DatabaseName, [CPU_Time_Ms],
       CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPUPercent]
FROM DB_CPU_Stats
WHERE DatabaseID > 4 -- system databases
AND DatabaseID <> 32767 -- ResourceDB
ORDER BY row_num OPTION (RECOMPILE);

Maintenance DBCC Commands

Maintenance DBCC statements are used to gather information about the general health of the database as well as specific database objects and to repair minor database corruption issues.

•DBCC CHECKALLOC
•DBCC CHECKCATALOG
•DBCC CHECKCONSTRAINTS
•DBCC CHECKDB
•DBCC CHECKFILEGROUP
•DBCC CHECKTABLE
•DBCC CLEANTABLE

•DBCC DBREINDEX
•DBCC INDEXDEFRAG
•DBCC NEWALLOC
•DBCC SHOWCONTIG
•DBCC SHOW_STATISTICS
•DBCC SHRINK_DATABASE
•DBCC SHRINKFILE

Move Tables Between The File Groups

1.Check  Table is exist on which filegroup

SELECT d.name AS FileGroup
 FROM sys.filegroups d
 JOIN sys.indexes i
   ON i.data_space_id = d.data_space_id
 JOIN sys.tables t
   ON t.object_id = i.object_id
WHERE i.index_id<2                     -- could be heap or a clustered table
 AND t.name= 'Employee'
 AND t.schema_id = schema_id('HumanResources')

Add another data file to the database in a separate file group.Then create the index by running the below query.

2.Create Index

CREATE UNIQUE CLUSTERED INDEX PK_Employee
    ON HumanResources.Employee(LoginID)
    WITH (DROP_EXISTING = ON) ON [Test_Data_2]

 CREATE CLUSTERED INDEX PK_Employee
    ON HumanResources.Employee(LoginID)
    WITH (DROP_EXISTING = ON) ON [Test_Data_2]

Then again execute the query-1.You will noticed the table is now pointing to the second datafile.