SQL Server Locks

When a process in SQL Server performs an operation (query or data modification) against a table, it performs a lock on it. There are different types of locks depending on the operation and its configuration: Live locks: Temporary locks on a table. Dead/cyclic Blocks: It is when two transactions are locking two tables, and each is waiting for the other to complete. When[…]

Database architecture: Files, Shcemas, File Groups

Database files: The following types of files need to be taken into consideration when designing physical architecture: Datafiles: Primary and secondary. Also Log and backup files. It always recommended to split tempDB and non-clustered indices files in different disks. Also to have secondary data files for the purpose of better data file management. Should one use the[…]

High Availability with SQL Server Denali (2012)

SQL Server has several solutions for High Availability/Disaster Recovery, among which are: – Log Shipping: Jobs regularly shipping log files from principal server and secondary server(s). Secondary servers are usually unusable during this operation. Manual fail-over is required upon principal server failure. Uncommitted transactions can be shipped. – Mirroring: Internal mechanisms shipping logs from principal server to[…]

Useful Queries for Index usage and fragmentation

I just developed two queries I found very useful for detecting index usage and fragmentation: Index Usage: SELECT Db_name(database_id)     DB,        Object_name(u.object_id) [Table],        i.NAME                   [Index],        type_desc                IndexType,        user_seeks,        user_scans,        user_lookups,        user_updates FROM   sys.dm_db_index_usage_stats u        INNER JOIN sys.indexes i                ON u.object_id = i.object_id                   AND u.index_id = i.index_id Compare index Seeks vs. Lookups vs Scans. Seeks mean the queries are using and fully benefiting from the index, so seeks are good. Lookups mean that the queries are[…]

Index Fragementation: Detections and Solutions

As data in table change, its indexes -pointers to such data- age out. The aging process is called index Fragmentation. To detect index fragmentation, query this DMV: dm_db_index_physical_stats. I recommend using this query. To fix fragmentation, you have multiple options. Here are two solutions: Reorganize index: This causes pages, not data in pages, to be reorganized. Simple[…]

SQL Server Indexes

What is an index A SQL index is a mechanism that helps fetching query data more efficiently by building order structure pointing to data locations. These pointers help reduce the data search process. Index Types There are two main types of indexes in SQL server: 1. Clustered indexes: These cause the table rows to be physically sorted in[…]

SQL Server 2012 Database Restore

Syntax RESTORE FILELISTONLY | DATABASE | LOG db1 FROM DISK=‘c:db1.bak’ WITH REPLACE, MOVE ‘mdf1’ TO ‘db1.mdf’, MOVE ‘ldf1’ TO ‘db1.ldf’,  NORECOVERY | STANDBY=‘c:..txt’ filelistonly: Doesn’t restore any data. Rather only lists the files contained in the .bak file. replace: Replaces existing online database (if it exists) with the one to restore. move: By default, .bak files also store their own DB name and file restore locations. To modify that,[…]

SQL Server 2012 Backup Types

Backup is part of High Availability and Disaster Recovery. SQL Server has several types of Backups: • Full: Saves a copy of the .mdf (data+commited log) to disk, Resets/Deletes DCM*, and keeps .log. • Differential: Saves DCM, keeps Log. Restore:Full> latest differential backup. • Log (recovery model: full/bulk Log): Save log>Delete log. Restore: Full>Point-in-Time (PiT) Log.[…]