|
|
-
|
|
1. Never ever use Select * in your query.
- If you use *, the engine has to get all the column information from the table. If your table is huge and the column contain huge data, it may lead to IO bottleneck.
- There is a good prone to break your code if some one adds a column to the table when y......
|
|
-
|
|
It is often a requirement to understand the size of indexes on a table.
Here is a script shared by one of my friend at my workspace... looks very useful!!!
DECLARE @OBJECT_NAME VARCHAR(255) = 'trn_defect_tracking_history';
DECLARE @temp TABLE
(
indexID BIGINT,
......
|
|
-
|
|
The below script provides a complete possible information on a table.
/*
First Resultset(Table Information)
Table Name
ObjectId
Table Type
Is replicated table or not
Row count of the table
Second Resultset(Column information)
Third Resultset(Index Information)
Name, Id, Seeks, scans, upda......
|
|
-
|
|
Here with a script to convert the XML dead lock file to a table format as we DBA love the table fundamentally...
SELECT QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' + QUOTENAME(t.name) AS TableName,
c.name AS ColumnName,
TYPE_NAME(c.system_type_id) AS 'DataType',
IDENT_CURRENT(SCHEMA_NAME(t.sc......
|
|
-
|
|
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......
|
|