Dead Lock: when two transactions are holding resources and both of them require before completion.
Once the dead lock situation is detected lock manager make one of the transactions as victim for rollback and raise error 1205 which is captured/handled properly
we can check Dead Lock , Blocking and Locking processes using bleow:
1. sp_who
2. sp_who2
3. select * from sys.sysprocesses
4. select * from Sys.dm_tran_locks
5. Using activity montor(in sql server 2008 only)
6. Using SQL Server Profiler(using template TSQL_Locks)
7. sp_lock
8. select * from master.dbo.syslockinfo
Tuesday, March 13, 2012
Locks and Dead Lock in SQL Server
Locks and Dead Lock in SQL Server 2008:
SQL Server uses locking and blocking mechanism to maintain data consistency in multiuser environment. Lock Manager is the SQL Server Internal governing body to handle locking scenarios like:
1. Determine Appropriate lock to be acquire/Release on resource
2. How much time will retain the lock
3. Decide when processes are allowed to modify data so that reads should be consistent
4. I. When SQL Server started with lock set to 0,lock manager allocate 2 percent of the memory allocated for SQL Server for an initial pool of lock structure(SQL 2000)
II. When SQL Server started with lock set to 0,lock manager allocate appropriate percent of the memory allocated for SQL Server for an initial pool of l 2500 lock structure(SQL 2005) as soon as lock pool is exhausted lock manager request for additional memory
III. The dynamic lock pool does not acquire more than 60 percent of the memory allocated to the Database Engine. After the lock pool has reached 60 percent of the memory acquired by an instance of the Database Engine, or no more memory is available on the computer, further requests for locks generate an error
5. Resolve Dead locks
6. compatibility between locks
SQL Server has seven different locking modes and three different lock types:
Locking modes are:
1. Shared locks(S): placed on resource for read operation and compatible for other shared and update lock except exclusive lock. When repeatable read or locking hint is used then shared lock is retained till duration of the transaction otherwise shared lock released as soon as read is completed
Equation: S on S = OK, S on U = OK, S on X = WAIT
2. Update Lock(U): placed on resource where shared lock is required and need to upgrade to exclusive lock. one transaction can acquire update lock on resource once the modification is required on resource update lock upgrade to exclusive lock
Equation: U on S = OK, U on U = WAIT, U on X = WAIT
3. Exclusive Lock(X): placed on resource for data modification and not compatible with any other type of lock .only nolock hint and read uncommitted isolation level overrides exclusive lock functionality.
Equation: X on S = WAIT, X on U = WAIT, X on X = WAIT
4. Intent(IS,IX,SIX):placed on resource to improve performance and locking efficiency by placing at high level i.e. table level
5. Schema(Sch-M,Sch-S):Sch-M is placed on resource while schema modification like while adding column to a table and Sch-S are placed on object/resource while query are being compiled or executed.
Equation: Sch-M Block all other operations until lock is released
Sch-S Locks are not compatible with Sch-M Locks
6. Bulk Update(BU):Placed on resource for Bulk insert and update of the data, these locks allow multiple bulk insert threads to access table but does not allow other processes to access the table,BU locks is enable by using TABLOCK hint or by using sp_tableoption for table lock for bulk load table option.
7. Key Range: Placed on the range of rows to protect against phantom insertion/deletion in a record set accessed by transaction, these locks are used by transaction using SERILIZABLE transaction isolation level.
These three different lock mode can be acquire at Row, Page, Table level and scope
Up to session, transaction, and cursor.
Dead Lock: when two transactions are holding resources and both of them require before completion.
Once the dead lock situation is detected lock manager make one of the transactions as victim for rollback and raise error 1205 which is captured/handled properly.
SQL Server uses locking and blocking mechanism to maintain data consistency in multiuser environment. Lock Manager is the SQL Server Internal governing body to handle locking scenarios like:
1. Determine Appropriate lock to be acquire/Release on resource
2. How much time will retain the lock
3. Decide when processes are allowed to modify data so that reads should be consistent
4. I. When SQL Server started with lock set to 0,lock manager allocate 2 percent of the memory allocated for SQL Server for an initial pool of lock structure(SQL 2000)
II. When SQL Server started with lock set to 0,lock manager allocate appropriate percent of the memory allocated for SQL Server for an initial pool of l 2500 lock structure(SQL 2005) as soon as lock pool is exhausted lock manager request for additional memory
III. The dynamic lock pool does not acquire more than 60 percent of the memory allocated to the Database Engine. After the lock pool has reached 60 percent of the memory acquired by an instance of the Database Engine, or no more memory is available on the computer, further requests for locks generate an error
5. Resolve Dead locks
6. compatibility between locks
SQL Server has seven different locking modes and three different lock types:
Locking modes are:
1. Shared locks(S): placed on resource for read operation and compatible for other shared and update lock except exclusive lock. When repeatable read or locking hint is used then shared lock is retained till duration of the transaction otherwise shared lock released as soon as read is completed
Equation: S on S = OK, S on U = OK, S on X = WAIT
2. Update Lock(U): placed on resource where shared lock is required and need to upgrade to exclusive lock. one transaction can acquire update lock on resource once the modification is required on resource update lock upgrade to exclusive lock
Equation: U on S = OK, U on U = WAIT, U on X = WAIT
3. Exclusive Lock(X): placed on resource for data modification and not compatible with any other type of lock .only nolock hint and read uncommitted isolation level overrides exclusive lock functionality.
Equation: X on S = WAIT, X on U = WAIT, X on X = WAIT
4. Intent(IS,IX,SIX):placed on resource to improve performance and locking efficiency by placing at high level i.e. table level
5. Schema(Sch-M,Sch-S):Sch-M is placed on resource while schema modification like while adding column to a table and Sch-S are placed on object/resource while query are being compiled or executed.
Equation: Sch-M Block all other operations until lock is released
Sch-S Locks are not compatible with Sch-M Locks
6. Bulk Update(BU):Placed on resource for Bulk insert and update of the data, these locks allow multiple bulk insert threads to access table but does not allow other processes to access the table,BU locks is enable by using TABLOCK hint or by using sp_tableoption for table lock for bulk load table option.
7. Key Range: Placed on the range of rows to protect against phantom insertion/deletion in a record set accessed by transaction, these locks are used by transaction using SERILIZABLE transaction isolation level.
These three different lock mode can be acquire at Row, Page, Table level and scope
Up to session, transaction, and cursor.
Dead Lock: when two transactions are holding resources and both of them require before completion.
Once the dead lock situation is detected lock manager make one of the transactions as victim for rollback and raise error 1205 which is captured/handled properly.
Wednesday, January 11, 2012
SQL Server Bottleneck Analysis Guidelines
SQL Server Bottleneck Analysis Guidelines
Memory Bottleneck Analysis 2
Disk Bottleneck Analysis 4
Processor Bottleneck Analysis 6
Overall SQL Server Bottleneck Analysis 8
Transaction Management 9
Key performance counters 10
Memory Bottleneck Analysis
Object: - Memory
Counter: - Available Mbytes
Preferred Value: - > 20MB
Reference: - KB 889654
Object: - Memory
Counter: - Free System Page Table Entries
Preferred Value: - > 7000
Description: - Free System Page Table Entries is the number of page table entries not currently in use by the system. If < 7000, consider removing /3GB.
Reference: - KB 311901
Object: - Memory
Counter: - Pages/Sec
Preferred Value: - < 50
Description: - Pages/sec is the rate at which pages are read from or written to disk to resolve hard page faults. This counter is a primary indicator of the kinds of faults that cause system-wide delays.
Reference: - Monitoring and Tuning Your Server
Object: - Memory
Counter: - Pages Input/Sec
Preferred Value: - < 10
Description: - Pages Input/sec is the rate at which pages are read from disk to resolve hard page faults.
Reference: - KB 889654
Object: - Paging File
Counter: - %Usage
Preferred Value: - < 70%
Description: - The amount of the Page File instance in use in percent.
Reference: - KB 889654
Object: - SQLServer:Buffer Manager
Counter: - Page Life Expectancy
Preferred Value: - > 300
Description: - This performance monitor counter tells you, on average, how long data pages are staying in the buffer. If this value gets below 300 seconds, this is a potential indication that your SQL Server could use more memory in order to boost performance.
Object: - SQLServer:Buffer Manager
Counter: - Lazy Writes/Sec
Preferred Value: - < 20
Description: - This counter tracks how many times a second that the Lazy Writer process is moving dirty pages from the buffer to disk in order to free up buffer space. Generally speaking, this should not be a high value, say more than 20 per second or so. Ideally, it should be close to zero. If it is zero, this indicates that your SQL Server's buffer cache is plenty big and SQL Server doesn't have to free up dirty pages, instead waiting for this to occur during regular checkpoints. If this value is high, then a need for more memory is indicated.
Object: - SQLServer:Buffer Manager
Counter: - Page reads/sec
Preferred Value: - < 90
Description: - Number of physical database page reads issued. 80 – 90 per second is normal, anything that is above indicates indexing or memory constraint.
Object: - SQLServer:Buffer Manager
Counter: - Checkpoint Pages/Sec
Preferred Value: - This value is relative, it varies from server to server, we need to compare the average to a base line capture to tell if the value is high or low.
Description: - When a checkpoint occurs, all dirty pages are written to disk. This is a normal procedure and will cause this counter to rise during the checkpoint process. What you don't want to see is a high value for this counter over time. This can indicate that the checkpoint process is running more often than it should, which can use up valuable server resources. If this has a high figure (and this will vary from server to server), consider adding more RAM to reduce how often the checkpoint occurs, or consider increasing the "recovery interval" SQL Server configuration setting.
Object: - SQLServer:Buffer Manager
Counter: - Page writes/sec
Preferred Value: - < 90
Description: - Number of physical database page writes issued. 80 – 90 per second is normal, anything more we need to check the lazy writer/sec and checkpoint counters, if these counters are also relatively high then, it’s memory constraint.
Object: - SQLServer:Buffer Manager
Counter: - Free pages
Preferred Value: - > 640
Description: - Total number of pages on all free lists.
Object: - SQLServer:Buffer Manager
Counter: - Stolen pages
Preferred Value: - Varies. Compare with baseline
Description: - Number of pages used for miscellaneous server purposes (including procedure cache).
Object: - SQLServer:Buffer Manager
Counter: - Buffer Cache hit ratio
Preferred Value: - > 90%
Description: - Percentage of pages that were found in the buffer pool without having to incur a read from disk.
Object: - SQLServer:Buffer Manager
Counter: - Target Server Memory(KB)
Preferred Value: -
Description: - Total amount of dynamic memory the server can consume.
Object: - SQLServer:Buffer Manager
Counter: - Total Server Memory(KB)
Preferred Value: -
Description: - Total amount of dynamic memory (in kilobytes) that the server is using currently
Disk Bottleneck Analysis
Object: - PhysicalDisk
Counter: - Avg. Disk Sec/Read
Preferred Value: - < 8ms
Description: - Measure of disk latgency. Avg. Disk sec/Read is the average time, in seconds, of a read of data from the disk.
More Info:
Reads or non cached Writes
Excellent < 08 Msec ( .008 seconds )
Good < 12 Msec ( .012 seconds )
Fair < 20 Msec ( .020 seconds )
Poor > 20 Msec ( .020 seconds )
Cached Writes Only
Excellent < 01 Msec ( .001 seconds )
Good < 02 Msec ( .002 seconds )
Fair < 04 Msec ( .004 seconds )
Poor > 04 Msec ( .004 seconds
Object: - PhysicalDisk
Counter: - Avg. Disk sec/Write
Preferred Value: - < 8ms (non cached) < 1ms (cached)
Description: - Measure of disk latency. Avg. Disk sec/Write is the average time, in seconds, of a write of data to the disk.
Object: - PhysicalDisk
Counter: - Avg. Disk Read Queue Length
Preferred Value: - < 2 * spindles
Description: - Avg. Disk Read Queue Length is the average number of read requests that were queued for the selected disk during the sample interval.
More Info:
< (2+ no of spindles) Excellent
< (2*no of spindles) Good
< (3* no of spindles) Fair
Reference - Whitepaper “Performance Monitoring in Windows 2003: Best Practices” by Ben W. Christenbury
Note: If the disk has say 20 disk and it is RAID 10 then no. of spindles = 20/2 = 10. If it is RAID 5 then the no. of spindles = no of disks = 20.
Object: - PhysicalDisk
Counter: - Avg. Disk Write Queue Length
Preferred Value: - < 2 * spindles
Description: - Avg. Disk Write Queue Length is the average number of write requests that were queued for the selected disk during the sample interval.
Object: - SQLServer:Buffer Manager
Counter: - Page reads/sec
Preferred Value: - < 90
Description: - Number of physical database page reads issued. 80 – 90 per second is normal, anything that is above indicates indexing or memory constraint.
Object: - SQLServer:Buffer Manager
Counter: - Page writes/sec
Preferred Value: - < 90
Description: - Number of physical database page writes issued. 80 – 90 per second is normal, anything more we need to check the lazy writer/sec and checkpoint counters, if these counters are also relatively high then, it’s memory constraint.
Object: - SQLServer:Buffer Manager
Counter: - Free pages
Preferred Value: - > 640
Description: - Total number of pages on all free lists.
Object: - SQLServer:Buffer Manager
Counter: - Stolen pages
Preferred Value: - Varies. Compare with baseline
Description: - Number of pages used for miscellaneous server purposes (including procedure cache).
Object: - SQLServer:Buffer Manager
Counter: - Buffer Cache hit ratio
Preferred Value: - > 90%
Description: - Percentage of pages that were found in the buffer pool without having to incur a read from disk.
Processor Bottleneck Analysis
Object: - Processor
Counter: - %Processor Time
Preferred Value: - < 80%
Description: - % Processor Time is the percentage of elapsed time that the processor spends to execute a non-Idle thread.
Object: - Processor
Counter: - %Privileged Time
Preferred Value: - < 30% of Total %Processor Time
Description: - % Privileged Time is the percentage of elapsed time that the process threads spent executing code in privileged mode.
Object: - Process (sqlservr)
Counter: - %Processor Time
Preferred Value: - < 80%
Object: - Process (sqlservr)
Counter: - %Privileged Time
Preferred Value: - < 30% of %Processor Time (sqlservr)
Description: - Note: Divide the value by number of processors
Object: - System
Counter: - Context Switches/sec
Preferred Value: - < 3000
Description: -
1500 – 3000 per processor Excellent – Fair
> 6000 per processor Poor
Upper limit is about 40,000 at 90 % CPU per CPU
NOTE: Remember to divide by number of processors
Object: - System
Counter: - Processor Queue Length
Preferred Value: - < 4 per CPU
Description: - For standard servers with long Quantums
<= 4 per CPU Excellent
< 8 per CPU Good
< 12 per CPU Fair
Object: - SQLServer:Access Methods
Counter: - Full Scans / sec
Preferred Value: - < 1
Description: - If we see high CPU then we need to investigate this counter, otherwise if the full scan are on small tables we can ignore this counter. Values greater than 1 or 2 indicate that we are having table / Index page scans. We need to analyze how this can be avoided.
Object: - SQLServer:Access Methods
Counter: - Worktables Created/Sec
Preferred Value: - < 20
Description: - Number of worktables created in tempdb per second. Worktables are used for queries that use various spools (table spool, index spool, etc).
Object: - SQLServer:Access Methods
Counter: - Workfiles Created/Sec
Preferred Value: - < 20
Description: - Number of work files created per second. Tempdb workfiles are used in processing hash operations when the amount of data being processed is too big to fit into the available memory. They may be able to reduce this number by making the queries more efficient by adding/changing indexes, adding additional memory, etc.
Object: - SQLServer:Access Methods
Counter: - Page Splits/sec
Preferred Value: - < 20
Description: - Interesting counter that can lead us to our table / index design. This value needs to be low as possible. If you find out that the number of page splits is high, consider increasing the fillfactor of your indexes. An increased fillfactor helps to reduce page splits because there is more room in data pages before it fills up and a page split has to occur.
Overall SQL Server Bottleneck Analysis
Object: - SQLServer:General Statistics
Counter: - User Connections
Preferred Value: -
Description: - The number of users currently connected to the SQL Server.
Object: - SQLServer:General Statistics
Counter: - Logins/sec
Preferred Value: - < 2
Description: - > 2 per second indicates that the application is not correctly using connection pooling.
Object: - SQLServer:General Statistics
Counter: - Logouts/sec
Preferred Value: - < 2
Description: - > 2 per second indicates that the application is not correctly using connection pooling.
Object: - SQLServer:SQL Statistics
Counter: - Batch Requests/Sec
Preferred Value: - < 1000
Description: - Over 1000 batch requests per second indicate a very busy SQL Server.
Object: - SQLServer:SQL Statistics
Counter: - SQL Compilations/sec
Preferred Value: - < 10% of the number of Batch Requests / sec
Description: - The number of times per second that SQL Server compilations have occurred. This value needs to be as low as possible. If you see a high value such as over 100, then it’s an indication that there are lots or adhoc queries that are running, might cause CPU
Object: - SQLServer:SQL Statistics
Counter: - SQL Re-Compilations/sec
Preferred Value: - < 10% of the number of SQL Compilations/sec
Description: - This needs to be nil in our system as much as possible. A recompile can cause deadlocks and compile locks that are not compatible with any locking type.
Object: - SQLServer:Latches
Counter: - Average Latch Wait Time (ms)
Preferred Value: - < 300
Description: - Average latch wait time (milliseconds) for latch requests that had to wait.
Transaction Management
Object: - SQLServer:Locks
Counter: - Number of Deadlocks/sec
Preferred Value: - < 1
Description: - The number of lock requests that resulted in a deadlock.
Object: - SQLServer:Locks
Counter: - Lock Requests/sec
Preferred Value: - < 1000
Description: - Number of requests for a type of lock per second. Lock requests/sec > 1000 indicates that the queries are accessing large number of rows; the next step is to review high read queries. If you also see high Avg. Wait time, then it’s an indication of blocking, then review the blocking script output.
Object: - SQLServer:Locks
Counter: - Average Wait Time (ms)
Preferred Value: - < 500
Description: - This is the average wait time in milliseconds to acquire a lock. Lower the value the better it is. If the value goes higher then 500, there may be blocking going on; we need to run blocker script to identify blocking.
Key performance counters
Buffer cache hit ratio
The buffer cache hit ratio is the percentage of pages found in the buffer cache without having to read from disk. A value of 90 percent or higher is desirable. If the value is 60 percent, you should add more memory until the value is consistently greater than 90 percent.
Page life expectancy
Have you ever checked out the page life expectancy counter in Performance Monitor's Buffer Manager object? SQL Server Books Online (BOL) says the page life expectancy value is the "number of seconds a page will stay in the buffer pool without references." So, a buffer that has a 300-second page life expectancy will keep any given page in memory in the buffer pool for 5 minutes before the buffer pool flushes the page to disk—unless a process references the page.
This counter can be helpful in determining whether you have a memory problem, giving you a reasonably accurate view of whether your server has memory pressure. According to Microsoft, 300 seconds is the minimum target for page life expectancy. If the buffer pool flushes your pages in less than 300 seconds, you probably have a memory problem. Looking at this value is particularly handy when your page life expectancy is significantly higher or lower than 300 seconds. For example, a customer recently asked me, "Do we need more memory?" I monitored the page life expectancy value for the customer's system, and the value didn't fall below 3000 seconds. That's quantifiable proof that more memory wouldn't help performance. Other customers have an average page life expectancy between 10 and 50 seconds, and they wonder why their servers are slow.
Memory Bottleneck Analysis 2
Disk Bottleneck Analysis 4
Processor Bottleneck Analysis 6
Overall SQL Server Bottleneck Analysis 8
Transaction Management 9
Key performance counters 10
Memory Bottleneck Analysis
Object: - Memory
Counter: - Available Mbytes
Preferred Value: - > 20MB
Reference: - KB 889654
Object: - Memory
Counter: - Free System Page Table Entries
Preferred Value: - > 7000
Description: - Free System Page Table Entries is the number of page table entries not currently in use by the system. If < 7000, consider removing /3GB.
Reference: - KB 311901
Object: - Memory
Counter: - Pages/Sec
Preferred Value: - < 50
Description: - Pages/sec is the rate at which pages are read from or written to disk to resolve hard page faults. This counter is a primary indicator of the kinds of faults that cause system-wide delays.
Reference: - Monitoring and Tuning Your Server
Object: - Memory
Counter: - Pages Input/Sec
Preferred Value: - < 10
Description: - Pages Input/sec is the rate at which pages are read from disk to resolve hard page faults.
Reference: - KB 889654
Object: - Paging File
Counter: - %Usage
Preferred Value: - < 70%
Description: - The amount of the Page File instance in use in percent.
Reference: - KB 889654
Object: - SQLServer:Buffer Manager
Counter: - Page Life Expectancy
Preferred Value: - > 300
Description: - This performance monitor counter tells you, on average, how long data pages are staying in the buffer. If this value gets below 300 seconds, this is a potential indication that your SQL Server could use more memory in order to boost performance.
Object: - SQLServer:Buffer Manager
Counter: - Lazy Writes/Sec
Preferred Value: - < 20
Description: - This counter tracks how many times a second that the Lazy Writer process is moving dirty pages from the buffer to disk in order to free up buffer space. Generally speaking, this should not be a high value, say more than 20 per second or so. Ideally, it should be close to zero. If it is zero, this indicates that your SQL Server's buffer cache is plenty big and SQL Server doesn't have to free up dirty pages, instead waiting for this to occur during regular checkpoints. If this value is high, then a need for more memory is indicated.
Object: - SQLServer:Buffer Manager
Counter: - Page reads/sec
Preferred Value: - < 90
Description: - Number of physical database page reads issued. 80 – 90 per second is normal, anything that is above indicates indexing or memory constraint.
Object: - SQLServer:Buffer Manager
Counter: - Checkpoint Pages/Sec
Preferred Value: - This value is relative, it varies from server to server, we need to compare the average to a base line capture to tell if the value is high or low.
Description: - When a checkpoint occurs, all dirty pages are written to disk. This is a normal procedure and will cause this counter to rise during the checkpoint process. What you don't want to see is a high value for this counter over time. This can indicate that the checkpoint process is running more often than it should, which can use up valuable server resources. If this has a high figure (and this will vary from server to server), consider adding more RAM to reduce how often the checkpoint occurs, or consider increasing the "recovery interval" SQL Server configuration setting.
Object: - SQLServer:Buffer Manager
Counter: - Page writes/sec
Preferred Value: - < 90
Description: - Number of physical database page writes issued. 80 – 90 per second is normal, anything more we need to check the lazy writer/sec and checkpoint counters, if these counters are also relatively high then, it’s memory constraint.
Object: - SQLServer:Buffer Manager
Counter: - Free pages
Preferred Value: - > 640
Description: - Total number of pages on all free lists.
Object: - SQLServer:Buffer Manager
Counter: - Stolen pages
Preferred Value: - Varies. Compare with baseline
Description: - Number of pages used for miscellaneous server purposes (including procedure cache).
Object: - SQLServer:Buffer Manager
Counter: - Buffer Cache hit ratio
Preferred Value: - > 90%
Description: - Percentage of pages that were found in the buffer pool without having to incur a read from disk.
Object: - SQLServer:Buffer Manager
Counter: - Target Server Memory(KB)
Preferred Value: -
Description: - Total amount of dynamic memory the server can consume.
Object: - SQLServer:Buffer Manager
Counter: - Total Server Memory(KB)
Preferred Value: -
Description: - Total amount of dynamic memory (in kilobytes) that the server is using currently
Disk Bottleneck Analysis
Object: - PhysicalDisk
Counter: - Avg. Disk Sec/Read
Preferred Value: - < 8ms
Description: - Measure of disk latgency. Avg. Disk sec/Read is the average time, in seconds, of a read of data from the disk.
More Info:
Reads or non cached Writes
Excellent < 08 Msec ( .008 seconds )
Good < 12 Msec ( .012 seconds )
Fair < 20 Msec ( .020 seconds )
Poor > 20 Msec ( .020 seconds )
Cached Writes Only
Excellent < 01 Msec ( .001 seconds )
Good < 02 Msec ( .002 seconds )
Fair < 04 Msec ( .004 seconds )
Poor > 04 Msec ( .004 seconds
Object: - PhysicalDisk
Counter: - Avg. Disk sec/Write
Preferred Value: - < 8ms (non cached) < 1ms (cached)
Description: - Measure of disk latency. Avg. Disk sec/Write is the average time, in seconds, of a write of data to the disk.
Object: - PhysicalDisk
Counter: - Avg. Disk Read Queue Length
Preferred Value: - < 2 * spindles
Description: - Avg. Disk Read Queue Length is the average number of read requests that were queued for the selected disk during the sample interval.
More Info:
< (2+ no of spindles) Excellent
< (2*no of spindles) Good
< (3* no of spindles) Fair
Reference - Whitepaper “Performance Monitoring in Windows 2003: Best Practices” by Ben W. Christenbury
Note: If the disk has say 20 disk and it is RAID 10 then no. of spindles = 20/2 = 10. If it is RAID 5 then the no. of spindles = no of disks = 20.
Object: - PhysicalDisk
Counter: - Avg. Disk Write Queue Length
Preferred Value: - < 2 * spindles
Description: - Avg. Disk Write Queue Length is the average number of write requests that were queued for the selected disk during the sample interval.
Object: - SQLServer:Buffer Manager
Counter: - Page reads/sec
Preferred Value: - < 90
Description: - Number of physical database page reads issued. 80 – 90 per second is normal, anything that is above indicates indexing or memory constraint.
Object: - SQLServer:Buffer Manager
Counter: - Page writes/sec
Preferred Value: - < 90
Description: - Number of physical database page writes issued. 80 – 90 per second is normal, anything more we need to check the lazy writer/sec and checkpoint counters, if these counters are also relatively high then, it’s memory constraint.
Object: - SQLServer:Buffer Manager
Counter: - Free pages
Preferred Value: - > 640
Description: - Total number of pages on all free lists.
Object: - SQLServer:Buffer Manager
Counter: - Stolen pages
Preferred Value: - Varies. Compare with baseline
Description: - Number of pages used for miscellaneous server purposes (including procedure cache).
Object: - SQLServer:Buffer Manager
Counter: - Buffer Cache hit ratio
Preferred Value: - > 90%
Description: - Percentage of pages that were found in the buffer pool without having to incur a read from disk.
Processor Bottleneck Analysis
Object: - Processor
Counter: - %Processor Time
Preferred Value: - < 80%
Description: - % Processor Time is the percentage of elapsed time that the processor spends to execute a non-Idle thread.
Object: - Processor
Counter: - %Privileged Time
Preferred Value: - < 30% of Total %Processor Time
Description: - % Privileged Time is the percentage of elapsed time that the process threads spent executing code in privileged mode.
Object: - Process (sqlservr)
Counter: - %Processor Time
Preferred Value: - < 80%
Object: - Process (sqlservr)
Counter: - %Privileged Time
Preferred Value: - < 30% of %Processor Time (sqlservr)
Description: - Note: Divide the value by number of processors
Object: - System
Counter: - Context Switches/sec
Preferred Value: - < 3000
Description: -
1500 – 3000 per processor Excellent – Fair
> 6000 per processor Poor
Upper limit is about 40,000 at 90 % CPU per CPU
NOTE: Remember to divide by number of processors
Object: - System
Counter: - Processor Queue Length
Preferred Value: - < 4 per CPU
Description: - For standard servers with long Quantums
<= 4 per CPU Excellent
< 8 per CPU Good
< 12 per CPU Fair
Object: - SQLServer:Access Methods
Counter: - Full Scans / sec
Preferred Value: - < 1
Description: - If we see high CPU then we need to investigate this counter, otherwise if the full scan are on small tables we can ignore this counter. Values greater than 1 or 2 indicate that we are having table / Index page scans. We need to analyze how this can be avoided.
Object: - SQLServer:Access Methods
Counter: - Worktables Created/Sec
Preferred Value: - < 20
Description: - Number of worktables created in tempdb per second. Worktables are used for queries that use various spools (table spool, index spool, etc).
Object: - SQLServer:Access Methods
Counter: - Workfiles Created/Sec
Preferred Value: - < 20
Description: - Number of work files created per second. Tempdb workfiles are used in processing hash operations when the amount of data being processed is too big to fit into the available memory. They may be able to reduce this number by making the queries more efficient by adding/changing indexes, adding additional memory, etc.
Object: - SQLServer:Access Methods
Counter: - Page Splits/sec
Preferred Value: - < 20
Description: - Interesting counter that can lead us to our table / index design. This value needs to be low as possible. If you find out that the number of page splits is high, consider increasing the fillfactor of your indexes. An increased fillfactor helps to reduce page splits because there is more room in data pages before it fills up and a page split has to occur.
Overall SQL Server Bottleneck Analysis
Object: - SQLServer:General Statistics
Counter: - User Connections
Preferred Value: -
Description: - The number of users currently connected to the SQL Server.
Object: - SQLServer:General Statistics
Counter: - Logins/sec
Preferred Value: - < 2
Description: - > 2 per second indicates that the application is not correctly using connection pooling.
Object: - SQLServer:General Statistics
Counter: - Logouts/sec
Preferred Value: - < 2
Description: - > 2 per second indicates that the application is not correctly using connection pooling.
Object: - SQLServer:SQL Statistics
Counter: - Batch Requests/Sec
Preferred Value: - < 1000
Description: - Over 1000 batch requests per second indicate a very busy SQL Server.
Object: - SQLServer:SQL Statistics
Counter: - SQL Compilations/sec
Preferred Value: - < 10% of the number of Batch Requests / sec
Description: - The number of times per second that SQL Server compilations have occurred. This value needs to be as low as possible. If you see a high value such as over 100, then it’s an indication that there are lots or adhoc queries that are running, might cause CPU
Object: - SQLServer:SQL Statistics
Counter: - SQL Re-Compilations/sec
Preferred Value: - < 10% of the number of SQL Compilations/sec
Description: - This needs to be nil in our system as much as possible. A recompile can cause deadlocks and compile locks that are not compatible with any locking type.
Object: - SQLServer:Latches
Counter: - Average Latch Wait Time (ms)
Preferred Value: - < 300
Description: - Average latch wait time (milliseconds) for latch requests that had to wait.
Transaction Management
Object: - SQLServer:Locks
Counter: - Number of Deadlocks/sec
Preferred Value: - < 1
Description: - The number of lock requests that resulted in a deadlock.
Object: - SQLServer:Locks
Counter: - Lock Requests/sec
Preferred Value: - < 1000
Description: - Number of requests for a type of lock per second. Lock requests/sec > 1000 indicates that the queries are accessing large number of rows; the next step is to review high read queries. If you also see high Avg. Wait time, then it’s an indication of blocking, then review the blocking script output.
Object: - SQLServer:Locks
Counter: - Average Wait Time (ms)
Preferred Value: - < 500
Description: - This is the average wait time in milliseconds to acquire a lock. Lower the value the better it is. If the value goes higher then 500, there may be blocking going on; we need to run blocker script to identify blocking.
Key performance counters
Buffer cache hit ratio
The buffer cache hit ratio is the percentage of pages found in the buffer cache without having to read from disk. A value of 90 percent or higher is desirable. If the value is 60 percent, you should add more memory until the value is consistently greater than 90 percent.
Page life expectancy
Have you ever checked out the page life expectancy counter in Performance Monitor's Buffer Manager object? SQL Server Books Online (BOL) says the page life expectancy value is the "number of seconds a page will stay in the buffer pool without references." So, a buffer that has a 300-second page life expectancy will keep any given page in memory in the buffer pool for 5 minutes before the buffer pool flushes the page to disk—unless a process references the page.
This counter can be helpful in determining whether you have a memory problem, giving you a reasonably accurate view of whether your server has memory pressure. According to Microsoft, 300 seconds is the minimum target for page life expectancy. If the buffer pool flushes your pages in less than 300 seconds, you probably have a memory problem. Looking at this value is particularly handy when your page life expectancy is significantly higher or lower than 300 seconds. For example, a customer recently asked me, "Do we need more memory?" I monitored the page life expectancy value for the customer's system, and the value didn't fall below 3000 seconds. That's quantifiable proof that more memory wouldn't help performance. Other customers have an average page life expectancy between 10 and 50 seconds, and they wonder why their servers are slow.
Thursday, July 7, 2011
Wednesday, July 6, 2011
SQL Server Reporting Services
1. Question : What can SQL Server Reporting Services do?
SQL Server Reporting Service is one of the server-based software systems that generate reports developed by Microsoft. It is used for preparing and delivering interactive and variety of printed reports. It is administered through an interface that is web based. Reporting services utilizes a web service interface for supporting and developing of customized reporting applicatons. It can be competed with Crystal Reports and other business intelligent tools.
2. Question : Explain the architecture of reporting services?
Reporting services architecture is comprises of integrated components. It is multi-tiered, included with application, server and data layers. This architecture is scalable and modular. A single installation can be used across multiple computers. It includes the following components:
- Report Manager, Reporting Designer, Browser Types Supported by Reporting services, Report server, Report server command line utilities, Report Server Database, Reporting Services Extensibility, Data sources that is supported by Reporting Services.
3. Question : Describe Reporting Lifecycle?
The Reporting Lifecycle includes
- Report designing – The designing is done in Visual Studio Report Designer. It generates a class which embodies the Report Definition.
- Report processing – The processing includes binging the report definition with data from the report data source. It performs on all grouping, sorting and filtering calculations. The expressions are evaluated except the page header, footer and section items. Later it fires the Binding event and Bound event. As a result of the processing, it produces Report Instance. Report instance may be persisted and stored which can be rendered at a later point of time.
- Report Rendering: Report rendering starts by passing the Report Instance to a specific rendering extension (HTML or PDF formats). The instance of reports is paged if paging supported by output format. The expressions of items are evaluated in the page header and footer sections for every page. As a final step, the report is rendered to the specific output document.
4.Question : What are the ways to tune Reporting Services?
To tune-up the Reporting Services, follow the below mentioned ways:
- Expand the Server or utilizing the reporting services of another database server. For better embedding of report contents, report application’s logic and characteristics can have a duplicate copy of data.
- Replication of data continuously. Using nolock, the issues of locking can well be resolved and the performance of the query can be improved. This can be done by using dirty read at the time of duplicating the data is unavailable
5.How to upgrade report from SQL Server 2000 to SQL Server 2005?
You can use the following steps:
1. Back up ReportServer and ReportServerTemDB.
2. Back up encryption keys from Reporting services 2000 by using RsKeyMgmt.
3. Restore the databases backed up from step 1.
4. Connect to newly restored databases from step 3 from Reporting Services 2005 Configuration manager and database setup.
5. By using the provided credentials, Reporting Services will connect to new databases and perform the upgrade operations.
6. Confirm Reporting services upgrading the database and granting adequate rights to the credential account.
7. Restore backed up keys from step 2. Confirm that everything is displayed as green now.
SQL Server Reporting Service is one of the server-based software systems that generate reports developed by Microsoft. It is used for preparing and delivering interactive and variety of printed reports. It is administered through an interface that is web based. Reporting services utilizes a web service interface for supporting and developing of customized reporting applicatons. It can be competed with Crystal Reports and other business intelligent tools.
2. Question : Explain the architecture of reporting services?
Reporting services architecture is comprises of integrated components. It is multi-tiered, included with application, server and data layers. This architecture is scalable and modular. A single installation can be used across multiple computers. It includes the following components:
- Report Manager, Reporting Designer, Browser Types Supported by Reporting services, Report server, Report server command line utilities, Report Server Database, Reporting Services Extensibility, Data sources that is supported by Reporting Services.
3. Question : Describe Reporting Lifecycle?
The Reporting Lifecycle includes
- Report designing – The designing is done in Visual Studio Report Designer. It generates a class which embodies the Report Definition.
- Report processing – The processing includes binging the report definition with data from the report data source. It performs on all grouping, sorting and filtering calculations. The expressions are evaluated except the page header, footer and section items. Later it fires the Binding event and Bound event. As a result of the processing, it produces Report Instance. Report instance may be persisted and stored which can be rendered at a later point of time.
- Report Rendering: Report rendering starts by passing the Report Instance to a specific rendering extension (HTML or PDF formats). The instance of reports is paged if paging supported by output format. The expressions of items are evaluated in the page header and footer sections for every page. As a final step, the report is rendered to the specific output document.
4.Question : What are the ways to tune Reporting Services?
To tune-up the Reporting Services, follow the below mentioned ways:
- Expand the Server or utilizing the reporting services of another database server. For better embedding of report contents, report application’s logic and characteristics can have a duplicate copy of data.
- Replication of data continuously. Using nolock, the issues of locking can well be resolved and the performance of the query can be improved. This can be done by using dirty read at the time of duplicating the data is unavailable
5.How to upgrade report from SQL Server 2000 to SQL Server 2005?
You can use the following steps:
1. Back up ReportServer and ReportServerTemDB.
2. Back up encryption keys from Reporting services 2000 by using RsKeyMgmt.
3. Restore the databases backed up from step 1.
4. Connect to newly restored databases from step 3 from Reporting Services 2005 Configuration manager and database setup.
5. By using the provided credentials, Reporting Services will connect to new databases and perform the upgrade operations.
6. Confirm Reporting services upgrading the database and granting adequate rights to the credential account.
7. Restore backed up keys from step 2. Confirm that everything is displayed as green now.
TOP 50 New Features in SQL Server 2008
•Transparent Data Encryption
Enable encryption of an entire database, data files, or log files, without the need for application changes. Benefits of this include: Search encrypted data using both range and fuzzy searches, search secure data from unauthorized users, and data encryption without any required changes in existing applications.
•Extensible Key Management
SQL Server 2005 provides a comprehensive solution for encryption and key management. SQL Server 2008 delivers an excellent solution to this growing need by supporting third-party key management and HSM products.
•Auditing
Create and manage auditing via DDL, while simplifying compliance by providing more comprehensive data auditing. This enables organizations to answer common questions, such as, "What data was retrieved?"
•Enhanced Database Mirroring
SQL Server 2008 builds on SQL Server 2005 by providing a more reliable platform that has enhanced database mirroring, including automatic page repair, improved performance, and enhanced supportability.
•Automatic Recovery of Data Pages
SQL Server 2008 enables the principal and mirror machines to transparently recover from 823/824 types of data page errors by requesting a fresh copy of the suspect page from the mirroring partner transparently to end users and applications.
•Log Stream Compression
Database mirroring requires data transmissions between the participants of the mirroring implementations. With SQL Server 2008, compression of the outgoing log stream between the participants delivers optimal performance and minimizes the network bandwidth used by database mirroring.
•Resource Governor
Provide a consistent and predictable response to end users with the introduction of Resource Governor, allowing organizations to define resource limits and priorities for different workloads, which enable concurrent workloads to provide consistent performance to their end users.
•Predictable Query Performance
Enable greater query performance stability and predictability by providing functionality to lock down query plans, enabling organizations to promote stable query plans across hardware server replacements, server upgrades, and production deployments.
•Data Compression
Enable data to be stored more effectively, and reduce the storage requirements for your data. Data compression also provides significant performance improvements for large I/O bound workloads, like data warehousing.
•Hot Add CPU
Dynamically scale a database on demand by allowing CPU resources to be added to SQL Server 2008 on supported hardware platforms without forcing any downtime on applications. Note that SQL Server already supports the ability to add memory resources online.
•Policy-Based Management
Policy-Based Management is a policy-based system for managing one or more instances of SQL Server 2008. Use this with SQL Server Management Studio to create policies that manage entities on the server, such as the instance of SQL Server, databases, and other SQL Server objects.
•Streamlined Installation
SQL Server 2008 introduces significant improvements to the service life cycle for SQL Server through the re-engineering of the installation, setup, and configuration architecture. These improvements separate the installation of the physical bits on the hardware from the configuration of the SQL Server software, enabling organizations and software partners to provide recommended installation configurations.
•Performance Data Collection
Performance tuning and troubleshooting are time-consuming tasks for the administrator. To provide actionable performance insights to administrators, SQL Server 2008 includes more extensive performance data collection, a new centralized data repository for storing performance data, and new tools for reporting and monitoring.
•Language Integrated Query (LINQ)
Enable developers to issue queries against data, using a managed programming language, such as C# or VB.NET, instead of SQL statements. Enable seamless, strongly typed, set-oriented queries written in .NET languages to run against ADO.NET (LINQ to SQL), ADO.NET DataSets (LINQ to DataSets), the ADO.NET Entity Framework (LINQ to Entities), and to the Entity Data Service Mapping provider. Use the new LINQ to SQL provider that enables developers to use LINQ directly on SQL Server 2008 tables and columns.
•ADO.NET Data Services
The Object Services layer of ADO.NET enables the materialization, change tracking, and persistence of data as CLR objects. Developers using the ADO.NET framework can program against a database, using CLR objects that are managed by ADO.NET. SQL Server 2008 introduces more efficient, optimized support that improves performance and simplifies development.
•DATE/TIME
SQL Server 2008 introduces new date and time data types:
◦DATE—A date-only type
◦TIME—A time-only type
◦DATETIMEOFFSET—A time-zone-aware datetime type
◦DATETIME2—A datetime type with larger fractional seconds and year range than the existing DATETIME type
The new data types enable applications to have separate data and time types while providing large data ranges or user defined precision for time values.
•HIERARCHY ID
Enable database applications to model tree structures in a more efficient way than currently possible. New system type HierarchyId can store values that represent nodes in a hierarchy tree. This new type will be implemented as a CLR UDT, and will expose several efficient and useful built-in methods for creating and operating on hierarchy nodes with a flexible programming model.
•FILESTREAM Data
Allow large binary data to be stored directly in an NTFS file system, while preserving an integral part of the database and maintaining transactional consistency. Enable the scale-out of large binary data traditionally managed by the database to be stored outside the database on more cost-effective storage without compromise.
•Integrated Full Text Search
Integrated Full Text Search makes the transition between Text Search and relational data seamless, while enabling users to use the Text Indexes to perform high-speed text searches on large text columns.
•Sparse Columns
NULL data consumes no physical space, providing a highly efficient way of managing empty data in a database. For example, Sparse Columns allows object models that typically have numerous null values to be stored in a SQL Server 2005 database without experiencing large space costs.
•Large User-Defined Types
SQL Server 2008 eliminates the 8-KB limit for User-Defined Types (UDTs), allowing users to dramatically expand the size of their UDTs.
•Spatial Data Types
Build spatial capabilities into your applications by using the support for spatial data.
◦Implement Round Earth solutions with the geography data type. Use latitude and longitude coordinates to define areas on the Earth's surface.
◦Implement Flat Earth solutions with the geometry data type. Store polygons, points, and lines that are associated with projected planar surfaces and naturally planar data, such as interior spaces.
•Backup Compression
Keeping disk-based backups online is expensive and time-consuming. With SQL Server 2008 backup compression, less storage is required to keep backups online, and backups run significantly faster since less disk I/O is required.
•Partitioned Table Parallelism
Partitions enable organizations to manage large growing tables more effectively by transparently breaking them into manageable blocks of data. SQL Server 2008 builds on the advances of partitioning in SQL Server 2005 by improving the performance on large partitioned tables.
•Star Join Query Optimizations
SQL Server 2008 provides improved query performance for common data warehouse scenarios. Star Join Query optimizations reduce query response time by recognizing data warehouse join patterns.
•Grouping Sets
Grouping Sets is an extension to the GROUP BY clause that lets users define multiple groupings in the same query. Grouping Sets produces a single result set that is equivalent to a UNION ALL of differently grouped rows, making aggregation querying and reporting easier and faster.
•Change Data Capture
With Change Data Capture, changes are captured and placed in change tables. It captures complete content of changes, maintains cross-table consistency, and even works across schema changes. This enables organizations to integrate the latest information into the data warehouse.
•MERGE SQL Statement
With the introduction of the MERGE SQL Statement, developers can more effectively handle common data warehousing scenarios, like checking whether a row exists, and then executing an insert or update.
•SQL Server Integration Services (SSIS) Pipeline Improvements
Data Integration packages can now scale more effectively, making use of available resources and managing the largest enterprise-scale workloads. The new design improves the scalability of runtime into multiple processors.
•SQL Server Integration Services (SSIS) Persistent Lookups
The need to perform lookups is one of the most common ETL operations. This is especially prevalent in data warehousing, where fact records need to use lookups to transform business keys to their corresponding surrogates. SSIS increases the performance of lookups to support the largest tables.
•Analysis Scale and Performance
SQL Server 2008 drives broader analysis with enhanced analytical capabilities and with more complex computations and aggregations. New cube design tools help users streamline the development of the analysis infrastructure enabling them to build solutions for optimized performance.
•Block Computations
Block Computations provides a significant improvement in processing performance enabling users to increase the depth of their hierarchies and complexity of the computations.
•Writeback
New MOLAP enabled writeback capabilities in SQL Server 2008 Analysis Services removes the need to query ROLAP partitions. This provides users with enhanced writeback scenarios from within analytical applications without sacrificing the traditional OLAP performance.
•Enterprise Reporting Engine
Reports can easily be delivered throughout the organization, both internally and externally, with simplified deployment and configuration. This enables users to easily create and share reports of any size and complexity.
•Internet Report Deployment
Customers and suppliers can effortlessly be reached by deploying reports over the Internet.
•Manage Reporting Infrastructure
Increase supportability and the ability to control server behaviour with memory management, infrastructure consolidation, and easier configuration through a centralized store and API for all configuration settings.
•Report Builder Enhancements
Easily build ad-hoc and author reports with any structure through Report Designer.
•Forms Authentication Support
Support for Forms authentication enables users to choose between Windows and Forms authentication.
•Report Server Application Embedding
Report Server application embedding enables the URLs in reports and subscriptions to point back to front-end applications.
•Microsoft Office Integration
SQL Server 2008 provides new Word rendering that enables users to consume reports directly from within Microsoft Office Word. In addition, the existing Excel renderer has been greatly enhanced to accommodate the support of features, like nested data regions, sub-reports, as well as merged cell improvements. This lets users maintain layout fidelity and improves the overall consumption of reports from Microsoft Office applications.
•Predictive Analysis
SQL Server Analysis Services continues to deliver advanced data mining technologies. Better Time Series support extends forecasting capabilities. Enhanced Mining Structures deliver more flexibility to perform focused analysis through filtering as well as to deliver complete information in reports beyond the scope of the mining model. New cross-validation enables confirmation of both accuracy and stability for results that you can trust. Furthermore, the new features delivered with SQL Server 2008 Data Mining Add-ins for Office 2007 empower every user in the organization with even more actionable insight at the desktop
Enable encryption of an entire database, data files, or log files, without the need for application changes. Benefits of this include: Search encrypted data using both range and fuzzy searches, search secure data from unauthorized users, and data encryption without any required changes in existing applications.
•Extensible Key Management
SQL Server 2005 provides a comprehensive solution for encryption and key management. SQL Server 2008 delivers an excellent solution to this growing need by supporting third-party key management and HSM products.
•Auditing
Create and manage auditing via DDL, while simplifying compliance by providing more comprehensive data auditing. This enables organizations to answer common questions, such as, "What data was retrieved?"
•Enhanced Database Mirroring
SQL Server 2008 builds on SQL Server 2005 by providing a more reliable platform that has enhanced database mirroring, including automatic page repair, improved performance, and enhanced supportability.
•Automatic Recovery of Data Pages
SQL Server 2008 enables the principal and mirror machines to transparently recover from 823/824 types of data page errors by requesting a fresh copy of the suspect page from the mirroring partner transparently to end users and applications.
•Log Stream Compression
Database mirroring requires data transmissions between the participants of the mirroring implementations. With SQL Server 2008, compression of the outgoing log stream between the participants delivers optimal performance and minimizes the network bandwidth used by database mirroring.
•Resource Governor
Provide a consistent and predictable response to end users with the introduction of Resource Governor, allowing organizations to define resource limits and priorities for different workloads, which enable concurrent workloads to provide consistent performance to their end users.
•Predictable Query Performance
Enable greater query performance stability and predictability by providing functionality to lock down query plans, enabling organizations to promote stable query plans across hardware server replacements, server upgrades, and production deployments.
•Data Compression
Enable data to be stored more effectively, and reduce the storage requirements for your data. Data compression also provides significant performance improvements for large I/O bound workloads, like data warehousing.
•Hot Add CPU
Dynamically scale a database on demand by allowing CPU resources to be added to SQL Server 2008 on supported hardware platforms without forcing any downtime on applications. Note that SQL Server already supports the ability to add memory resources online.
•Policy-Based Management
Policy-Based Management is a policy-based system for managing one or more instances of SQL Server 2008. Use this with SQL Server Management Studio to create policies that manage entities on the server, such as the instance of SQL Server, databases, and other SQL Server objects.
•Streamlined Installation
SQL Server 2008 introduces significant improvements to the service life cycle for SQL Server through the re-engineering of the installation, setup, and configuration architecture. These improvements separate the installation of the physical bits on the hardware from the configuration of the SQL Server software, enabling organizations and software partners to provide recommended installation configurations.
•Performance Data Collection
Performance tuning and troubleshooting are time-consuming tasks for the administrator. To provide actionable performance insights to administrators, SQL Server 2008 includes more extensive performance data collection, a new centralized data repository for storing performance data, and new tools for reporting and monitoring.
•Language Integrated Query (LINQ)
Enable developers to issue queries against data, using a managed programming language, such as C# or VB.NET, instead of SQL statements. Enable seamless, strongly typed, set-oriented queries written in .NET languages to run against ADO.NET (LINQ to SQL), ADO.NET DataSets (LINQ to DataSets), the ADO.NET Entity Framework (LINQ to Entities), and to the Entity Data Service Mapping provider. Use the new LINQ to SQL provider that enables developers to use LINQ directly on SQL Server 2008 tables and columns.
•ADO.NET Data Services
The Object Services layer of ADO.NET enables the materialization, change tracking, and persistence of data as CLR objects. Developers using the ADO.NET framework can program against a database, using CLR objects that are managed by ADO.NET. SQL Server 2008 introduces more efficient, optimized support that improves performance and simplifies development.
•DATE/TIME
SQL Server 2008 introduces new date and time data types:
◦DATE—A date-only type
◦TIME—A time-only type
◦DATETIMEOFFSET—A time-zone-aware datetime type
◦DATETIME2—A datetime type with larger fractional seconds and year range than the existing DATETIME type
The new data types enable applications to have separate data and time types while providing large data ranges or user defined precision for time values.
•HIERARCHY ID
Enable database applications to model tree structures in a more efficient way than currently possible. New system type HierarchyId can store values that represent nodes in a hierarchy tree. This new type will be implemented as a CLR UDT, and will expose several efficient and useful built-in methods for creating and operating on hierarchy nodes with a flexible programming model.
•FILESTREAM Data
Allow large binary data to be stored directly in an NTFS file system, while preserving an integral part of the database and maintaining transactional consistency. Enable the scale-out of large binary data traditionally managed by the database to be stored outside the database on more cost-effective storage without compromise.
•Integrated Full Text Search
Integrated Full Text Search makes the transition between Text Search and relational data seamless, while enabling users to use the Text Indexes to perform high-speed text searches on large text columns.
•Sparse Columns
NULL data consumes no physical space, providing a highly efficient way of managing empty data in a database. For example, Sparse Columns allows object models that typically have numerous null values to be stored in a SQL Server 2005 database without experiencing large space costs.
•Large User-Defined Types
SQL Server 2008 eliminates the 8-KB limit for User-Defined Types (UDTs), allowing users to dramatically expand the size of their UDTs.
•Spatial Data Types
Build spatial capabilities into your applications by using the support for spatial data.
◦Implement Round Earth solutions with the geography data type. Use latitude and longitude coordinates to define areas on the Earth's surface.
◦Implement Flat Earth solutions with the geometry data type. Store polygons, points, and lines that are associated with projected planar surfaces and naturally planar data, such as interior spaces.
•Backup Compression
Keeping disk-based backups online is expensive and time-consuming. With SQL Server 2008 backup compression, less storage is required to keep backups online, and backups run significantly faster since less disk I/O is required.
•Partitioned Table Parallelism
Partitions enable organizations to manage large growing tables more effectively by transparently breaking them into manageable blocks of data. SQL Server 2008 builds on the advances of partitioning in SQL Server 2005 by improving the performance on large partitioned tables.
•Star Join Query Optimizations
SQL Server 2008 provides improved query performance for common data warehouse scenarios. Star Join Query optimizations reduce query response time by recognizing data warehouse join patterns.
•Grouping Sets
Grouping Sets is an extension to the GROUP BY clause that lets users define multiple groupings in the same query. Grouping Sets produces a single result set that is equivalent to a UNION ALL of differently grouped rows, making aggregation querying and reporting easier and faster.
•Change Data Capture
With Change Data Capture, changes are captured and placed in change tables. It captures complete content of changes, maintains cross-table consistency, and even works across schema changes. This enables organizations to integrate the latest information into the data warehouse.
•MERGE SQL Statement
With the introduction of the MERGE SQL Statement, developers can more effectively handle common data warehousing scenarios, like checking whether a row exists, and then executing an insert or update.
•SQL Server Integration Services (SSIS) Pipeline Improvements
Data Integration packages can now scale more effectively, making use of available resources and managing the largest enterprise-scale workloads. The new design improves the scalability of runtime into multiple processors.
•SQL Server Integration Services (SSIS) Persistent Lookups
The need to perform lookups is one of the most common ETL operations. This is especially prevalent in data warehousing, where fact records need to use lookups to transform business keys to their corresponding surrogates. SSIS increases the performance of lookups to support the largest tables.
•Analysis Scale and Performance
SQL Server 2008 drives broader analysis with enhanced analytical capabilities and with more complex computations and aggregations. New cube design tools help users streamline the development of the analysis infrastructure enabling them to build solutions for optimized performance.
•Block Computations
Block Computations provides a significant improvement in processing performance enabling users to increase the depth of their hierarchies and complexity of the computations.
•Writeback
New MOLAP enabled writeback capabilities in SQL Server 2008 Analysis Services removes the need to query ROLAP partitions. This provides users with enhanced writeback scenarios from within analytical applications without sacrificing the traditional OLAP performance.
•Enterprise Reporting Engine
Reports can easily be delivered throughout the organization, both internally and externally, with simplified deployment and configuration. This enables users to easily create and share reports of any size and complexity.
•Internet Report Deployment
Customers and suppliers can effortlessly be reached by deploying reports over the Internet.
•Manage Reporting Infrastructure
Increase supportability and the ability to control server behaviour with memory management, infrastructure consolidation, and easier configuration through a centralized store and API for all configuration settings.
•Report Builder Enhancements
Easily build ad-hoc and author reports with any structure through Report Designer.
•Forms Authentication Support
Support for Forms authentication enables users to choose between Windows and Forms authentication.
•Report Server Application Embedding
Report Server application embedding enables the URLs in reports and subscriptions to point back to front-end applications.
•Microsoft Office Integration
SQL Server 2008 provides new Word rendering that enables users to consume reports directly from within Microsoft Office Word. In addition, the existing Excel renderer has been greatly enhanced to accommodate the support of features, like nested data regions, sub-reports, as well as merged cell improvements. This lets users maintain layout fidelity and improves the overall consumption of reports from Microsoft Office applications.
•Predictive Analysis
SQL Server Analysis Services continues to deliver advanced data mining technologies. Better Time Series support extends forecasting capabilities. Enhanced Mining Structures deliver more flexibility to perform focused analysis through filtering as well as to deliver complete information in reports beyond the scope of the mining model. New cross-validation enables confirmation of both accuracy and stability for results that you can trust. Furthermore, the new features delivered with SQL Server 2008 Data Mining Add-ins for Office 2007 empower every user in the organization with even more actionable insight at the desktop
Subscribe to:
Posts
(
Atom
)