Browse by Tags

All Tags » sql server » blocking (RSS)
SQL Server 2005 Connectivity, Security policies & Network protocols: simple practice to follow, after installation?
Published 23 May 8 3:44 AM | SSQA.net
Secured by default, secured by design is the buzzword and catchy stuff for any Enterprise IT administrators. Then it comes about standards and policies to follow such as ISO , with the recent threats (last 10 years) and vulnerabilities within IT world...
Filed under: , , , , , ,
Error while executing DMV - Msg 102, Level 15, State 1, Line 3 Incorrect syntax near '('.
Published 17 March 8 4:39 AM | SSQA.net
DMV - has got very special place for DBAs since the inception of SQL Server 2005. In this case one of the thme is very important to find out the index statistics within your database, such as sys.dm_db_index_physical_stats statement. Recently one of the...
Filed under: , , , , , , , , , ,
SQL Server 2008 - Activity Monitor a new key to DBA!
Published 11 March 8 3:14 AM | SSQA.net
Have you looked at the Activity Monitor screen within the Managment Studio? I feel this is somewhat surprise features included from February CTP (or was it there before?). This is more useful to the DBAs to have a look at resource activity and locking...
Filed under: , , , , , , , ,
SQL Server Performance - what it takes for a newbie DBA in resolving performance issues?
Published 5 February 8 2:57 AM | SSQA.net
SQL Server Performance Tuning, sounds familiar on a DBA perspective and though it sounds like an easy words but when you start analyzing the problem it is far deeper inside. When you refer to the web resources such as forums/newsgroups then a common question...
Filed under: , , , , , , , , , ,
SQL server Temporary Tables, Table Variables and Recompiles - interesting scenario
Published 17 January 8 2:23 AM | SSQA.net
Usage of Temporary tables or variables is a common scenario in SQL Server world, since SQL Server 2005 usage of TEMPDB references to the blog posts here: http://sqlserver-qa.net/blogs/tools/archive/2007/04/05/sql-server-index-optimization-best-practices...
Filed under: , , , , , , , , , , , ,
TSQL to findout blocking and locks on a SQL Server?
Published 7 December 7 1:8 PM | SSQA.net
Have you ever performed huge operations such as deleting records of a table and processing inserts on that table at the same time? This is a common task that every application will have to perform and you can avoid by fine tuning your queries (mostly...
Filed under: , , , , , , , ,
SQL Server 2005: Slow running queries - Blocking and Index problems
Published 24 September 7 9:19 AM | SSQA.net
As a DBA you must be aware that slow or long running queries can contribute to excessive resource consumption and be the consequence of blocked queries. No database application is exception to this behaviour, so the queries that also run slowly because...
Filed under: , , , , , ,
High CPU issues that are specific to SQL Server 2000 installations and few with SQL 2005 too!
Published 20 September 7 10:40 AM | SSQA.net
Here comes another Frequently Asked Question (FAQ) on the forums and Frequently Posted Blog (FPB) here about high cpu issues on the SQL Server 2000 instances. Looking at high cpu spiks and performace issues is a common problem in all SQL Server installations...
Filed under: , , , , , , , , , , , ,
TSQL to obtain a list of current execution of Parallel Plans (MAXDOP)
Published 4 September 7 9:32 AM | SSQA.net
PARALLELISM is a close friend of DBA where you get to see it as a common occurence in the multi-processor server environment and during such performance issues you can also investigate whether a parallel plan is in use. For instance If a particular query...
Filed under: , , , , , , , , ,
Performance - check whether it is affecting application or system, don't blame SQL Server alone
Published 3 September 7 9:42 AM | SSQA.net
Its a general assumption that whenever performance is degraded, the finger will be pointed to SQL Server, pretty easy eh!? If you look at any of the SQL Server related forums then 3 in 10 questions asks same question and nothing but shove blame on SQL...
Filed under: , , , , , , , , , ,
Dedicated Administrator Console (DAC) - saved an important day for a DBA
Published 4 August 7 9:9 AM | SSQA.net
Until recently I have just used Dedicated Administrator Console (DAC) to connect just to test whether the connection will be successful. You may be aware that only administrator will be able to access a running instance of SQL Server Database Engine to...
Filed under: , , , , , , , ,
Table fragmentation, Index contention and locking issues to resolve
Published 31 July 7 9:31 AM | SSQA.net
Yet another important factors that every DBA needs to concerned about table & index fragmentation within their SQL Server environment. Refer to the a rticle about DetectTableFragmentation in both 2000 and 2005 version. Fragmentation occurs due to...
Filed under: , , , , , , , , ,
Why is a SPID blocking itself in sysprocesses?
Published 27 July 7 3:29 AM | SSQA.net
This was the question asked during an interview that was attended by one of my friend, I'm not sure how he managed to answer but came to me asking how it is possible. For your information af ter you apply SQL 2000 Service Pack 4, you might observe...
Filed under: , , , ,
TSQL to generate blocking scenario for testing
Published 27 July 7 2:44 AM | SSQA.net
Most of the times you have observed to identify the blocking and how to resolve them. How about you need a script to generate a blocking scenario within your queries, this is to identify the blocker script is working or not. Also will help to test whether...
Filed under: , , , , , ,
FIX: Blocking and performance problems may occur when you enable trace flag 1118 in SQL Server 2005 if the temporary table creation workload is high
Published 22 May 7 12:42 PM | SSQA.net
The following pages were recently modified. Source: Knowledge Base Product: Microsoft SQL Server 2005 Standard Edition KBA - FIX: Blocking and performance problems may occur when you enable trace flag 1118 in SQL Server 2005 if the temporary table creation...
Filed under: , ,
How to find dirty buffer pages and clear them - SQL Server
Published 21 May 7 10:22 AM | SSQA.net
Dirty buffer pages are nothing but consuming more memory for even smaller queries on the SQL Server. By default the DBA shouldn't worry about these dirty pages in the buffer pool as they are taken care by the system using Write Ahead Logging ( WAL...
Filed under: , , , , , ,
SQL Server 2005 how to find a rarely used index?
Published 18 May 7 5:31 AM | SSQA.net
For a SQL Server database having an index is always beneficial and from performance point of view too. But how about the clustered index which is occupying space on the database that is not used by any of the queries in the database. The Query Optimizer...
Filed under: , , , , , ,
Row versioning-based transaction isolation features in SQL 2005
Published 6 May 7 4:47 AM | SqlServer-QA.net (SSQA)
More information about nonlocking, nonblocking read consistency to your users through snapshot isolation and read committed isolation using row versioning. Find out when you can use these features to improve performance and reduce latency in your applications...
Filed under: , , , ,