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
       SET STATISTICS IO ON
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

Monday, December 16, 2013

Analyzing sys.dm_db_index_physical_stats results.

Hi
In this post i am analyzing the "sys.dm_db_index_physical_stats" results by with some production data.
By seeing this we can come to below conclusions.
First condition is
1) Focus on those rows which has page_count >1000 on those rows you can come to below conclusions.

If fragmentation(avg_fragmentation_in_percent) is less than 5 % (avg_fragmentation_in_percent)--> Leave as it is
If fragmentation(avg_fragmentation_in_percent) is more than 5 % and less than 30% - Reorganize the index
If fragmentation(avg_fragmentation_in_percent) is more than 30% - Rebuild index
and PageDencity we can caculated based on "avg_page_space_used_in_percent" column but here it is very good and it nearly 80 percent. You have to check for those columns which has page_count is >1000

Monday, December 9, 2013

What is Sleeping/Awaiting Command Session

A session with the status of Sleeping/awaiting command is simply client connection with no active query to the sql server.The table below shows transitions from "running" to "sleeping" states for a session.

The question usually around a session that is holding locks and its state is sleeping/Awaiting command.If the client has a open transaction and the client did not submit Commit or Rollback command the state is showing as Sleeping/Awaiting command.

We can examine this by running the below query. Open a new query window in AdventureWorks database. And run the below query.
Now go to another window run the below query and see the state. It is in Sleeping mode.


Giving some more in depth explanation about :
A thread is using the CPU (called RUNNING) until it needs to wait for a resource. It then moves to an unordered list of threads that are SUSPENDED. In the meantime, the next thread on the FIFO (first-in-first-out) queue of threads waiting for the CPU (called being RUNNABLE) is given the CPU and becomes RUNNING. If a thread on the SUSPENDED list is notified that it’s resource is available, it becomes RUNNABLE and is put on the bottom of the RUNNABLE queue. Threads continue this clockwise movement from RUNNING to SUSPENDED to RUNNABLE to RUNNING again until the task is completed. You can see processes in these states using the sys.dm_exec_requests DMV.

Wednesday, November 20, 2013

SQL Server Activity Monitor Explanations.

The Activity Monitor gives you a view of current Connections on instance.The monitor can be used to determine whether you have any processes blocking other processes.To open Activity Monitor in Management Studio,right click on server in the object explorer,Then select Activity Monitor.

Session ID: The unique number assigned to a process connected to SQL Server. This is also called a SPID. An icon next to the number represents what is happening in the connection. If you see an hourglass, you can quickly tell that the process is waiting on or is being blocked by another connection.

User Process Flag: Indicates whether processes that are internal SQL Server processes are connected.These processes are filtered out by default. You can change the value to see the SQL Server internal processes by clicking the drop down and selecting the appropriate value.

Login: The login to which the process is tied.

Database: The current database context for the connection.

Task State: Indicates whether the user is active or sleeping. (No, this doesn’t mean the user is active or sleeping at his or her keyboard; it means that SQL Server keeps the connection active even though no activity is coming from the login until the user disconnects.)

Done: Completed.

Pending: The process is waiting for a worker thread.

Runnable: The process has previously been active, has a connection, but has no work to do.
Running: The process is currently performing work.

Suspended: The process has work to do, but it has been stopped. Additional information about why the process is suspended may be found in the Wait Type column.

Command: Shows the type of command currently being executed. For example, you may see SELECT, DBCC, INSERT, or AWAITING COMMAND here, to name a few. This won’t show you the actual query that the user is executing, but it does highlight what type of activity is being run on your server.Select a row in this table right click and choose details.

❑ Application: The application that is connecting to your instance. This can be set by the developer in the connection string.

❑ Wait Time (ms): If the process is being blocked or waiting for another process to complete, this indicates how long the process has been waiting, in milliseconds; it will have a value of 0 if the process is not waiting.

Wait Type: Indicates the event you are waiting on.

Wait Resource: The text representation of the resource you are waiting on.

Blocked By: The Session ID (SPID) that is blocking this connection.

Head Blocker: A value of 1 means the Blocked By Session ID is the head of the blocking chain,otherwise 0.

Memory Use (KB): The current amount of memory used by this connection. Number of pages in the Procedure cache attributed to this connection. Note this was reported in pages in prior releases.

Host: The login’s workstation or server name. This is a really useful item, but in some
cases you may have a Web server connecting to your SQL Server, which may make this less
important.

Workload Group: The name of the Resource Governor workload group for this query.

Tuesday, November 19, 2013

StartUp parameters description in SQL Service Properties

In the sql server configuration Manager once we find the services. Right click on the service and go to properties and Advanced tab. Check the start up parameters. Below are the description for the parameters.



-d switch specifies the database file
-l switch specifies the log file
-T which enables you to start given trace flags for all the connections for sql server instance.
To monitor trace flags –T1204 and to turn on trace flag to monitor deadlocks in the instance.
-f switch places sql server in minimal mode and only allow single connection. –f option correct the memory setting.
-g switch is used to reserve the additional memory outside SQL Server main memory pool for use by extended stored procedures. If the –g switch is not used the default of 256MB of memory is allocated to this area.
-m the “-m” switch puts sql server in single-user mode (sometimes called master recovery model) and suspends the CHECKPOINT process which writes data from disk to database device.
-k switch new to sql server 2008,is used influence the Checkpoint frequency.