SELECT*

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

Hi In this post i am analyzing the " sys.dm_db_index_physical_stats " function 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. the avg_fragmentation_in_percent value should be as close to zero as possible , and the avg_fragment_size_in_pages should be as high as possible. And also there are 2 mor...

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 ...

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: Indic...

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...

The below explanation about when we install sql services , we need to tell which service need to run on which. So the below are differences between the account. Domain Account: This is an active directory domain account that you create and is the preferred account type for SQL Server Services needing network access. Local System Account: This is highly privileged account you should not use for services. Local Service Account: This is special preconfigured account that has the same permission as members of the User Group.Network access is done as a null session with no credentials. Network Service Account: This account is the same as the Local Service Account,except that network access is allowed,credentialed as the computer account.Do not use this account for SQL Server or SQL Agent Service accounts. Local Server Account : This is a local Windows account that you create. This is the most secure method you can use for services that do not need network acces...

Please find the below image how CLUSTERED INDEX  works. And Its Architecture in the image.

I created .fmt file by running above command in the command prompt. Go to run click cmd and write the code above and click enter .FMT fie would be generate in the respective path.

I have the below versions in my stystem: Database Version:  SQL Developer Edition (64-bit) Windows Version: Windows 7 Enterprise (64-Bit) Excle:  Micorsoft Excel 2010 or 2007(64-Bit) Since all the versions are 64 Bit. So we need to install  Microsoft.ACE.OLEDB.12.0 from “AccessDatabaseEngine_x64”. instead of 'Microsoft.Jet.OLEDB.4.0'. We can download this from the below link http://www.microsoft.com/en-us/download/confirmation.aspx?id=13255 After downloading and running the setup. Run the below queries step by step. sp_configure 'show advanced options' , 1   GO   RECONFIGURE   GO   sp_configure 'Ad Hoc Distributed Queries' , 1   GO   RECONFIGURE ;   -----Run the below query after running above. EXEC master . dbo . sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1 GO EXEC master . dbo . ...

SELECT lok.resource_type ,lok.resource_subtype ,DB_NAME(lok.resource_database_id) ,lok.resource_description ,lok.resource_associated_entity_id ,lok.resource_lock_partition ,lok.request_mode ,lok.request_type ,lok.request_status ,lok.request_owner_type ,lok.request_owner_id ,lok.lock_owner_address ,wat.waiting_task_address ,wat.session_id ,wat.exec_context_id ,wat.wait_duration_ms ,wat.wait_type ,wat.resource_address ,wat.blocking_task_address ,wat.blocking_session_id ,wat.blocking_exec_context_id ,wat.resource_description FROM sys.dm_tran_locks lok JOIN sys.dm_os_waiting_tasks wat ON lok.lock_owner_address = wat.resource_address

SELECT db_name ( database_id ) AS 'Database' , count ( page_id ) AS 'Dirty Pages' FROM sys . dm_os_buffer_descriptors WHERE is_modified = 1 GROUP BY db_name ( database_id ) ORDER BY count ( page_id ) DESC