|
|
-
|
|
Identity saturation is one of a major check we need to have as a DBA for a big projects.The below script would detail the curretn identity and the percentage used for all table objects of a database.
SELECT QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' + QUOTENAME(t.name) AS TableName,
c.name AS Co......
|
|
-
|
|
Just thought of sharing an information about how to hide/show tables from your object explorer. The below query has to cut and paste in your query and “CNTRL+SHIFT+M” to input your parameters. One more thing… CNTRL+SHIFT+M work like a programming language giving us an opportunity ......
|
|
-
|
|
Update statistics are important for SQL server database as the query plan generation
heavily depends on the histogram of the data been passed for the very first execution.
The DBA should have given with sysadmin fixed server role or ownership of the database.
When do I need a ......
|
|
-
|
|
DBCC CHECK* would never cause a blocking as early days. SQL Server 2005 ownwards,
DBCC CHECK* works on snapshot.DBCC CHECK* will not cause any blocking on concurrency
as user process is never going to affect by DBCC and vice versa.However, as long
as we do DBCC operation on Sna......
|
|
-
|
|
Here Let us see what is the significance of of columns on a table.
Create a Test Table with 10 columns having first two column as default(for easy purpose)and the rest with null to test the table size.
Create Table Table_Size
(
Col1 int Default(1),
Col2 Varchar(50) Default 'Test',
Col3 Varc......
|
|
-
|
|
1. How much space been occupied by single adhoc plans?
The below query would give you information about how much space been used for single adhoc. This is a wastage of memeory.
We can think of some useful setting at server level to have Optimize for adhoc settings.
Optimize for adhoc will never s......
|
|
-
|
|
Blocking and locking is a main area for performance tuning and to ensure a good health of production system. Here is a script to notify DBAs about the locking occurs in the production system.It uses service brokers to notify the lock information.
As a first step, we need to set a threshold value fo......
|
|