Friday, November 21, 2014

SQL Data page and Extent.

Data Page:  The size of the data page is 8KB. Data rows are put on the page serially, starting immediately after the header. A row offset table starts at the end of the page. And each row offset table contains one entry for each row on the page. Each entry records how far the first byte of the row is from the start of the page. The entries in the row offset table are in reverse sequence from the sequence of the rows on the page.

Extent:  Extents are the basic unit in which space is managed. An extent is eight contiguous pages, or 64 KB. This means SQL Server database have 16 extents per megabyte (1MB). To make its space allocation efficient SQL Server does not allocate whole extents to table with small amounts of data. SQL Server has two types of events.
  1 Page->8KB
  8 Pages->8*8=64KB(One Extent)


Friday, September 12, 2014

RESTORATION OF ReportServer and ReportServerTempdb

I just want to share one of my experiences as DBA. One day my boss gave me a task of restoration of ‘Reportserver’ and ‘ReportServerTempdb’ databases. This is in SQL Server 2008R2. What I did is I tried to restore the database and I got an error saying that “The database is already in Use”. So I thought there might be other sessions are open on this database. So I ran sp_who2 stored procedure and kill all  the sessions which are connecting to ‘Reportserver’ database and try to restore the database as usual. But this time also I faced the same problem.

So I thought this time I will take the database in single user mode and try to restore the same. I failed in this attempt also. What I realized after some time was “Reporting Services” are running and this is stopping me to restore the database. I stopped this “Reporting Service” and restored the two databases with in one minute

Thursday, September 11, 2014

sp_configure for max server memory restriction.

I came across below errors in my event viewer log after installing SQL Server.

Error: 17887, Severity: 10, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.

There was a memory allocation failure during connection establishment. Reduce nonessential memory load, or increase system memory. The connection has been closed. [CLIENT: ]

SQL Server was unable to run a new system task, either because there is insufficient memory or the number of configured sessions exceeds the maximum allowed in the server. Verify that the server has adequate memory. Use sp_configure with option 'user connections' to check the maximum number of user connections allowed. Use sys.dm_exec_sessions to check the current number of sessions, including user processes.

BRKR TASK: Operating system error Exception 0x1 encountered.

Error: 17300, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.

Error: 6
SQL Server is terminating a system or background task SSB Task due to errors in starting up the task (setup state 1).

Before explaining about the above errors let me tell you about the system setup

Windows Level:  Window Server 2008 R2;64BIT;8GB RAM.

SQL Server Level: Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr  2 2010 15:48:46 Copyright (c) Microsoft Corporation.Standard Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1) (Hypervisor)

Troubleshooting Steps:  The problem that I am trying to explain is 97% percent of the RAM is occupied by SQL Server Process. And there is no space for other processes. Because please find the screen shots for the below reference.

By running the below command you can reduce the memory consumption of SQL Server
sp_configure 'show advanced options', 1;
sp_configure 'max server memory', 4096;


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.