Database Audit - Example with Select statement.

SQL SERVER audit is a server feature and its used to audit server actions.
Its available in SQL 2008 and later editions.
Its using SQL Server extended events to audit server activity.
Its providing much more granularity and we can use it to audit database related events also.

SQL Server 2008 R2 Developers Training Kit

For all those who are new to SQL Server BI or New to SQL Server 2008 R2 , Microsoft has launched  a training kit  SQL Server 2008 R2 Developers Training Kit. Its a great resource to used to SQL Server  2008 R2. It contains a rich set of presentations, demos, hands-on labs and videos that are perfect for self-paced learning or for conducting your own training.

We can download it from 

http://www.microsoft.com/download/en/details.aspx?id=16281

What's In the Training Kit
The training kit is divided into four sections:

• Getting Started (for Web and BI developers who are new to SQL Server)
• SQL Server 2008 (for experienced SQL Server developers who want to understand what's new in 2008)
• SQL Server 2008 R2 (for experienced SQL Server developers who want to understand what's new in 2008 R2)
• Office 2010 (for experienced BI developers who want to understand what's new in 2008 R2 and Office 2010)

Here is a breakdown of each section and its associated content:

1. Getting Started
   Web Developers: 1 Hands-On Lab, 2 Videos
   BI Developers: 32 Presentations, 27 Demos, 12 Hands-On Labs, 59 Videos
2. SQL Server 2008
   Overview: 1 Presentation, 1 Video
   Date and Time Data Types: 1 Presentation, 1 Demo
   Spatial Data Types: 1 Presentation, 2 Demos, 2 Hands-On Labs
   FILESTREAM Blob Storage: 1 Presentation, 2 Demos
   Transact-SQL Improvements: 1 Presentation, 4 Demos
   .NET CLR Integration: 1 Presentation, 2 Demos, 1 Hands-On Lab
   Reporting Services: 1 Presentation, 1 Demo
   AdventureWorks Racing All-Up SQL Server 2008 Demo: 1 Presentation, 1 Demo
3. SQL Server 2008 R2
   Overview: 2 Presentations, 4 Hands-On Labs, 8 Videos
   Data-tier Application Framework: 3 Presentations, 5 Demos, 2 Hands-On Labs, 9 Videos
   StreamInsight: 4 Presentations, 7 Demos, 3 Hands-On Labs, 17 Videos
   Reporting Services: 7 Prsentations, 5 Demos, 5 Hands-On Labs, 12 Videos
4. Office 2010
   Excel 2010 and PowerPivot: 4 Presentations, 4 Demos, 3 Hands-On Labs, 8 Videos
   SharePoint 2010, PerformancePoint Services and PowerPivot: 6 Presentations, 6 Demos, 3 Hands-On Labs, 12 Videos

Server details using DMV

Sometimes we need basic server  details , we can get this information from below DMV

SELECT *

FROM   sys.dm_os_sys_info

It provides valuable miscellaneous  hardware information which includes  number of logical cpu and hyperthread ratio for servers. Also provides information for physical memory , virtual memory  , buffer pool , sql server start time . In one of my previous article I have discussed how to get SQL server start time from tempdb
But from here we can get more accurate detail about sql server start time .

SELECT cpu_count                                         logical_cpu,

       hyperthread_ratio,

       cpu_count / hyperthread_ratio                     physcial_cpu,

       physical_memory_in_bytes / ( 1024 * 1024 * 1024 ) memory_in_gb,

       virtual_memory_in_bytes / ( 1024 * 1024 * 1024 )  virtual_memory_in_gb,

       bpool_committed                                   buffer_pool_committed,

       bpool_commit_target                               buffer_pool_commit_target,

       bpool_visible                                     buffer_pool_visible,

       sqlserver_start_time

FROM   sys.dm_os_sys_info

Use of resource governor to limit CPU usage for backup compression

Resource Governor

As many of you have gone through my previous article on compress backup
Since backup compression takes higher cpu it affect other processes

Backup Compression

Backup compression
Its a new feature in SQL Server 2008 Enterprise Edition.
Its support to compress a backup , for which we had to use third party tools earlier.

compression part 2

Compression in partitions

We can set compression property for individual partition .

Below are the points that we have to take consideration for parition operation.

Compression Part 1

If you are using Enterprise or Developer edition of SQL Server  2008 you can use compression on your server

estimate data saving by compression

Today I got a mail for how to get space saving estimation using compression


Well compression is supported in SQL Server 2008 Enterprise and Developer Edition.

Merge Statement - SQL Server 2008

I am going to tell you about the new feature in Sql server 2008 "MERGE"statement.

It is used to perform insert , update, delete statement in a single query on a target table by joining to a source table

Comound Operators SQL Server 2008

SQL 2008 now supports compound operators, which is available in other databases since long time

x += 2
is equivalent to x = x+2

Data Compression Results

SQL 2008 is supporting data compression.


Its a new feature in SQL 2008 which is not available in previous versions

Forceseek- Table Hint SQL 2oo8

Forceseek

It forces optimizer to use index seek only. Sometimes optimizer does not user proper plan and use index scan which cause high reads on the system. We can use forceseek here to force otpimizer to use index seek which can give better performance.

Index Suggestion by Query Execution Plan - SQL Server 2008

I hope many of us we have start to use SQL Server 2oo8. SQL Server 2oo8 comes with many silent features. We all are familiar of execution plan of a query. We are getting visual display of query execution and cost of each action. Its very useful to understand how query will be executed.

Merge 2- SQL Server 2008


I have given basic details about merge command in my first article


Merge SQL Server 2008

Merge

Table Value Constructors

Table Value Constructors for Insert Statement


CREATE TABLE test (

id INT,

name VARCHAR(100))