|
|
-
|
|
Transactions generate IO, latching and locking on tables and indexes , while attempting to access data. The sys.dm_db_index_operational_stats DMV returns aggregated data on this activity.
Warning : sys.dm_db_index_operational_stats returns data only as long ......
|
|
-
|
|
To generate the ALTER INDEX..DISABLE and the ALTER INDEX..REBUILD statements for all nonclustered indexes for a single table use the following sql statements.
Copy and Paste the statements, execute on the database. I’ve included the URL column for easy click through to some notes on the code
1......
|
|
-
|
|
Use the script below to rebuild an index on a table
ALTER INDEX MY_INDEX_NAME on MY_TABLE_NAME REBUILD;
Check SQL Server BOL for a the full list of options
Notes
1) Issuing an Index Rebuild request, drops and recreates the index
2) If rebuilding a CLUSTERED INDEX, nonclustered index......
|
|
-
|
|
Use the script below to disable an index on a SQL Server table
ALTER INDEX MY_INDEX_NAME on MY_TABLE_NAME DISABLE;
Executing the code will prevent access to the index.
If it’s a CLUSTERED INDEX, the data remains intact on the table, but no DML can access the data. Drop the index or rebuild......
|
|
-
|
|
Fragmentation of an index can severely affect performance. When logical ordering of the key within a page does not match the physical ordering within the data file, fragmentation exists.
I execute index maintenance scripts for databases on a regular basis. If I’m executing a custom job, such as ......
|
|
-
|
|
The Environment Variable DB2_RR_TO_RS enables\disables the Repeatable Read attribute. I's enabled for DB2 performance purposes
DB2 registry parameter DB2_RR_TO_RS is used to:
Set a default isolation level for user tables to Read Stability (RS) isolation level. The RS level is not dissimilar to......
|
|
-
|
|
Developers like to have some control over the development process. An example is index building. Asking a DBA repeatedly for changes to a Development Database might not be the best use of time – for either parties.
For example, a developer may want to work over the weekend to finish a Data Impor......
|
|
-
|
|
When to create a SQL Server non-clustered index? How do non-clustered indexes sit
with SQL Serrver clustered indexes?
It’s not the purpose of this post to describe a SQL Server non-clustered index,
which you will find in BOL
Firstly, the main differences with betw......
|
|
-
|
|
The regular comment passed by DBAs is “90 % of the overall db performance is based on the code written against it”.
There is truth to this comment, and large amounts are written on optimising queries on DB2 databases. DB2 database design physical supports logical database design.
Th......
|
|
-
|
|
False. Let’s look at the definition of a table variable:
DECLARE @myTableVar TABLE
(companyID INT NOT NULL,
companySize INT NOT NULL)
The constraints allowed on table variables are: PRIMARY KEY,UNIQUE KEY and NULL.
A PRIMARY KEY constraint can only be defined if certain criteria are......
|
|