Thursday, August 28, 2014

Replication Agents in SQL Server 2008.

SQL Server Agent jobs are key components of replication. A number of SQL Agent jobs are created by replication.

1) Snapshot Agent: The Snapshot Agent is a SQL Agent job that takes and applies the snapshot either for setting up transactional or merge replication or for a snapshot replication.

2) Log Reader Agent: The Log Reader Agent is the SQL Agent job that reads the transaction log on the publisher and records the transactions for each article being published into the distribution database.

3) Distribution Agent: The Distribution agent is the SQL Agent job that reads the transactions written to the distribution database and applies them to the subscribing database.

4) Merge Agent: The Merge agent is the SQL Agent job that manages activities of Merge replication.

5) Other Agents: You may come across quite a few other SQL Agent Jobs, as described in the following list

a) Queue Reader Agent
b) History Agent
c) Distribution Cleanup
d) Expired subscription cleanup
e) Replication agent checkup
f) Reinitialize failed subscription agent

Monday, August 25, 2014


Let us look at the STATISTICS IO output for the example of the query. This is a session level setting STATISTICS IO provides you with I/O related information for the statement you run.
SET STATISTICS IO ON--This is session level command
SELECT SalesOrderID,OrderDate,CustomerID
FROM dbo.New_SalesOrderHeader

If we run the above command in AdventureWorks2008 database we will get the results as below

Table ‘New_SalesOrderHeader’.
Scan count 1,
logical reads 799,
physical reads 0,
read-ahead reads 798,
lob logical reads 0,
lob physical reads 0,
lob read-ahead
reads 0.

Scan Count: Scan count tells you that how many times the table was accessed for this query.
Logical Reads: This counter indicates that how many pages were read from data cached. In this case total 799 pages read from data cache.
PhysicalReads: This counter indicates that the number pages read from the disk here it is 0 that means there is no physical read from the disk
Read-Ahead Reads: This counter indicates that number of pages from physical disk that were placed into the internal data cache when sql server guess that you will need them in the query.And here it is 798,which means that many physical reads. Both Physical read and read-ahead-reads counter indicates the amount of physical disk activity. The log logical reads,lop physical reads,and lob read ahead reads are the same as other reads but these counters indicates reads from large objects.


Tuesday, August 12, 2014


ipconfig /release
ipconfig /flushdns
ipconfig /renew

Friday, July 25, 2014

Optimizing SQL Server CPU Performance.

Performance Counter Counter Object Threshold Notes
% Processor Time Processor > 80% Potential causes include memory pressure, low query plan reuse, non-optimized queries.
Context Switches/sec System > 5000 x processors Potential causes include other applications on the server, more than one instance of SQL Server running on the same server, hyper-threading turned on.
Processor Queue Length System > 5 x processors Potential causes include other applications on the server, high compilations or recompilations, more than one instance of SQL Server running on the same server.
Compilations/sec SQLServer:SQL Statistics Trend Compare to Batch Requests/sec.
Re-Compilations/sec SQLServer:SQL Statistics Trend Compare to Batch Requests/sec.
Batch Request/sec SQLServer:SQL Statistics Trend Compare with the Compilation and Re-Compilations per second.
Page Life Expectancy SQLServer:Buffer Manager < 300 Potential for memory pressure.
Lazy Writes/sec SQLServer:Buffer Manager Trend Potential for large data cache flushes or memory pressure.
Checkpoints/sec SQLServer:Buffer Manager Trend Evaluate checkpoints against PLE and Lazy Writes/sec.
Cache Hit Ratio: SQL Plans SQLServer:Plan Cache < 70% Indicates low plan reuse.
Buffer Cache Hit Ratio SQLServer:Buffer Manager < 97% Potential for memory pressure.

Wednesday, July 9, 2014

Logical Read Vs Physical Read along with Buffer Hit Ratio.

Logical Reads: Reading data pages from Cache.
Physical Reads: Reading Data Pages from Hard Disk
Buffer Cache Hit Ratio: (logical reads-Physical Reads)/logical read*100%
Logical Reads:  Logical read indicates total number of data pages needed to be accessed from data cache to process a query. It is very possible that logical read will access the same data pages many times. So count of logical read value may be higher than actual number of pages in a table. Usually the best way to reduce logical read is to apply correct index or to rewrite the query.
Physical Reads: Physical read indicates the total number of pages that are read from disk. In case no data in data cache, the physical read will be equal to number of logical read. And usually it happens for first query request. And for subsequent same query request the number will be substantially decreased because the data pages have been in the data cache.
Buffer Cache Hit Ratio:  Buffer hit ratio will be calculated based on these two kinds of read as the following formula:
(Logical Reads-Physical Reads)/logical read*100%
The High Buffer hit ratio (if possible near to 100%) indicates good database performance on SQL Server level. So use information from Physical read and Buffer hit ratio to measure performance in server level and logical read to measure individual Query level.
Excess of the logical reads tends high memory usage. There are some ways by which we can reduce logical reads
1)      Improper/Useless/Insufficient Indexes:   Indexes should be built on the basis of data access or retrieval process if any of the indexes is built on the columns which are not used in a query will lead to High logical reads and will degrade the performance while reads and writing the data.
2)      Poor Fill Factor/Page Density:  Page use should not be very less .Otherwise large number of page will be used for small amount of data which will also leads to High Logical Reads
3)      Wide Indexes:  Indexing on the large number columns will lead to High logical Reads
4)       Index Scanning:  If a Query is leads to index scanning on the table then logical reads will be high.
How to get the Logical Read Count?
      Below are the ways to check logical Reads
2)  SYS.DM_EXEC_QUERY_STATS:  By executing the below statements we can find detailed info about read and write
SELECT * FROM sys.dm_exec_query_stats
3) SQL Profiler:  By executing the sql profiler on that database we can find out logical reads
There are also some other DMV’s which will aslo help us to get logical reads

Monday, January 13, 2014

"Log On Error"

Some times we my get "Log on Error" while we are trying to start the SQL Services.You can find the below error while you are trying to start "SQL Server Agent".And the reason would be we will periodically change the password for Windows.If we change the password for windows and if we did not change it to SQL Service. We may encounter this problem.So if we change the password for SQL Service similar to windows password then this problem would resolve.

Tuesday, December 17, 2013

Analyzing sys.dm_os_wait_stats

The below link is explaining about the "Wait Stats" of SQL Server which was described by Paul Randal.

Analyzing sys.dm_os_wait_stats