Watch & Subscribe my SQL videos on YouTube | Join me on Facebook

SQL Server 2012 (a.k.a. Denali) – Best Practices Analyzer

April 23, 2012 Leave a comment

Microsoft SQL Server 2012 released its Best Practices Analyzer (BPA) on 1st week of April and is available free for download at following [link].

-> Overview …as mentioned in the link

BPA is a diagnostic tool that performs the following functions:
1. Gathers information about a Server and a Microsoft SQL Server 2012 instance installed on that Server.
2. Determines if the configurations are set according to the recommended best practices.
3. Reports on all configurations, indicating settings that differ from recommendations.
4. Indicates potential problems in the installed instance of SQL Server.
5. Recommends solutions to potential problems.

-> System Requirements to install
– PowerShell V2.0
– Microsoft Baseline Configuration Analyzer V2.0

The download link also provides information about:
– Installation Instructions.
– And some Additional Information about its capabilities.

… stay tuned for more updates about SQL Server 2012.

Categories: Optimization Performance, SQL Server 2012 Tags: , , ,

SQL DBA – Check Isolation Level of a Database | DBCC USEROPTIONS

April 20, 2012 Leave a comment

While testing a functionality we had to run some Stored Procedures in parallel. So we executed all 4 SPs in different Sessions (separate windows). All SPs got executed successfully except one, this one ended up in a Deadlock.

I thought to check the Isolation level of database but my mind didn’t clicked instantly.
So I checked MS BOL about this and find the DBCC management command to find it, which is DBCC USEROPTIONS & as follows:

USE [AdventureWorks2012]
GO

DBCC USEROPTIONS
GO

Output:-

Set Option		Value
textsize		2147483647
language		us_english
dateformat		mdy
datefirst		7
lock_timeout		-1
quoted_identifier	SET
arithabort		SET
ansi_null_dflt_on	SET
ansi_warnings		SET
ansi_padding		SET
ansi_nulls		SET
concat_null_yields_null	SET
isolation level		read committed

Other Isolation level values it returns are:-

- read uncommitted
- read committed
- repeatable read
- serializable
- read committed snapshot
- snapshot

The sys.databases metadata View also contains a column i.e. is_read_committed_snapshot_on, which tells if READ_COMMITTED_SNAPSHOT Isolation level in ON or OFF.
Check this:

select is_read_committed_snapshot_on, *
from sys.databases
Categories: DBA Stuff Tags:

Microsoft Deployment Toolkit (MDT) 2012 Now Available | Reliable and flexible OS deployment

April 18, 2012 Leave a comment

The latest version of the Microsoft Deployment Toolkit—version 2012— is now available for download.
Visit the Download Center to download MDT 2012.

Deploy Windows 7, Office 2010, Windows 8 Consumer Preview, Windows Server “8” Beta and Windows Server 2008 R2 with the newly released Microsoft Deployment Toolkit (MDT) 2012. MDT 2012 is the newest version of Microsoft Deployment Toolkit, a Solution Accelerator for operating system and application deployment. MDT is the recommended process and toolset for automating Windows 7 and Office 365 deployments.

The Solution Accelerators Team added new features to MDT 2012 that include the ability for users to initiate and customize their own deployments using System Center Configuration Manager 2012, key enhancements in Windows 7 driver support, and much more.
MDT 2012 will provide new benefits such as:
• Comprehensive tools and guidance to efficiently manage large-scale deployments of Microsoft Office 2010.
• An enhanced User-Driven Installation (UDI) deployment method that utilizes System Center Configuration Manager 2012. UDI lets end users initiate and customize an OS deployment on their PCs—via an easy-to-use wizard.
• Ease Lite Touch installation with new capabilities to check on the status of currently running deployments.
• This release provides support for deploying Windows 8 Consumer Preview in a lab environment.

These features, combined with many bug fixes and other improvements, make MDT 2012 more reliable and flexible than ever.

Support for Configuration Manager 2012: MDT 2012 provides support for Configuration Manager 2012 releases. MDT 2012 fully leverages the capabilities provided by Configuration Manager 2012 for OS deployment. Users now also have the ability to migrate MDT 2012 task sequences from Configuration Manager 2007 to Configuration Manager 2012.

Customize deployment questions: For System Center Configuration Manager customers, MDT 2012 provides an improved, extensible wizard and designer for customizing deployment questions.

Ease Lite Touch installation: The Microsoft Diagnostics and Recovery Toolkit (DaRT) is now integrated with Lite Touch Installation, providing remote control and diagnostics. New monitoring capabilities are available to check on the status of currently running deployments. LTI now has an improved deployment wizard user experience. Enhanced partitioning support ensures that deployments work regardless of the current structure.

Secure Deployments: MDT 2012 offers integration with the Microsoft Security Compliance Manager (SCM) tool to ensure a secure Windows deployment from the start.

Reliability and flexibility: Existing MDT users will find more reliability and flexibility with the many small enhancements and bug fixes and a smooth and simple upgrade process.

Support for Windows 8: MDT 2012 provides support for deploying Windows 8 Consumer Preview and Windows Server “8” Beta in a lab environment.

Next steps:
• Download MDT 2012.
• Send your comments to the MDT Team.
• Learn more about the MDT.

Get the latest tips from Microsoft Solution Accelerators—in 140 characters or less! Follow us on Twitter: @MSSolutionAccel.

Categories: Microsoft Tags: , ,

SQL Server 2012 (a.k.a. Denali) | “SET FMTONLY ON/OFF” Deprecated | new SPs and DMFs Introduced

April 17, 2012 Leave a comment

While running SQL Server Profiler to know what’s running behind the Application you might have noticed “SET FMTONLY ON” with other SQL statements. Most of you would be knowing this, the main point of putting this post is to let people know that this feature is deprecated in Denali and not to use it in new development work.

“SET FMTONLY ON” Returns only metadata to the client. It can be used to test the format of the response without actually running the query.
Let’s check how it is being used and what it returns:

USE AdventureWorks2012;
GO

-- Simple SELECT without using the "SET FMTONLY ON", OFF by-default:
SELECT TOP 10 
	BusinessEntityID, Title, FirstName, MiddleName, LastName, Suffix, EmailPromotion
FROM [Person].[Person]

-- Using "SET FMTONLY":
SET FMTONLY ON;

SELECT TOP 10 
	BusinessEntityID, Title, FirstName, MiddleName, LastName, Suffix, EmailPromotion
FROM [Person].[Person]

SET FMTONLY OFF;
GO

Output:

This feature is deprecated in Denali and should not be used in production code.

Now in replacement to this Denali has introduced 4 new objects to get the same information in easy and more informative way.
These are as follows:

--> 1. SP: sp_describe_first_result_set
-- Returns the metadata for the first possible result set of the Transact-SQL batch.
EXEC sp_describe_first_result_set @tsql = N'SELECT * FROM HumanResources.Employee'

--> 2. SP: sp_describe_undeclared_parameters
-- Returns a result set that contains metadata about undeclared parameters in a Transact-SQL batch.
EXEC sp_describe_undeclared_parameters @tsql = N'SELECT * FROM HumanResources.Employee where BusinessEntityID = @id AND BirthDate > @dt'

--> 3. DMF: sys.dm_exec_describe_first_result_set()
-- This is a DMF (Dynamic Management Function) which takes a Transact-SQL statement as a parameter and describes the metadata of the first result set for the statement.
SELECT * FROM sys.dm_exec_describe_first_result_set (N'SELECT * FROM HumanResources.Employee', NULL, 0)
-- It is similar to the SP sp_describe_first_result_set mentioned above.

--> 4. DMF: sys.dm_exec_describe_first_result_set_for_object()
-- This is a DMF (Dynamic Management Function) which takes an @object_id as a parameter and describes the first result metadata for the module with that ID.
SELECT * FROM sys.dm_exec_describe_first_result_set_for_object(OBJECT_ID('uspGetEmployeeManagers'), 0)
-- It has same set definition as sys.dm_exec_describe_first_result_set() DMF mentioned above.


-- The last mentioned DMF can also be used with the sys.procedures to get the metadata of the Stored Procedures:
SELECT p.name, X.* 
FROM sys.procedures AS p
CROSS APPLY sys.dm_exec_describe_first_result_set_for_object(p.object_id, 0) AS X

… Just execute each one of these and check what all information they provide.

I really like these SPs and DM Functions, will be very useful in Metadata based based Dev work.

More info on this check MS BOL: http://msdn.microsoft.com/en-us/library/ms173839.aspx

Categories: SQL Server 2012, SQL Tips Tags: , , , , , , ,

SQL Server 2012 (a.k.a Denali) – New feature | Enhanced OVER() Clause – Part1

April 13, 2012 1 comment

OVER() clause in SQL Server was introduced in version 2005 and it was the same in 2008 and even 2008-R2.

Ranking functions like ROW_NUMBER(), RANK(), DENSE_RANK() & NTILE() can use the OVER() clause to Parition and Order a record-set before actually applying their logic. Thus a large record-set can be divided into several partitions and calculations can be applied to each set in a set-based approach rather than going with Loops or Cursors.

With the new version of SQL Server i.e. 2012 the OVER() clause has been extended completely to other aggregates, like SUM(), AVG(), etc. Prior to this the OVER() clause can be partially used for these Aggregate Functions.

Here we will see solving a classic problem of calculating Running Totals with this new feature.
I’ve already discussed about solving this problem with JOINS in version 2005,2008, 2008-R2 in my previous post at following [link].

Let’s see how can we use the SUM() Aggregate Function with OVER() clause to solve this:

USE [AdventureWorks2012]
GO

-- With 2012, calculating Running totals by using Aggregare function SUM() with extended Window Function OVER():
;with RunTot as (
	select 
		row_number() over(order by s.OrderDate) as row,
		s.SalesPersonID, p.FirstName, p.LastName, s.TotalDue, s.OrderDate
	from Sales.SalesOrderHeader s
	join Person.Person p
	on s.SalesPersonID = p.BusinessEntityID
	)
SELECT 
	SalesPersonID, FirstName, LastName, OrderDate, TotalDue, 
	SUM(TotalDue) OVER(partition by SalesPersonID ORDER BY row)
FROM RunTot
order by SalesPersonID, row
GO


-- Prior to 2012 calculating Running totals by using SELF-JOIN:
;with RunTot as (
	select 
		row_number() over(partition by s.SalesPersonID order by s.OrderDate) as row,
		s.SalesPersonID, p.FirstName, p.LastName, s.TotalDue, s.OrderDate
	from Sales.SalesOrderHeader s
	join Person.Person p
	on s.SalesPersonID = p.BusinessEntityID)
select a.row, a.SalesPersonID, a.FirstName, a.LastName, a.OrderDate, a.TotalDue, sum(b.TotalDue) as RunTotal
from RunTot a
join RunTot b on a.SalesPersonID = b.SalesPersonID and a.row >= b.row
group by a.row, a.SalesPersonID, a.FirstName, a.LastName, a.TotalDue, a.OrderDate
order by a.SalesPersonID, a.row

On comparing performance of both the queries within the batch:
– The first query with SUM() window function costs just 38%.
– While the second query without the SUM() window function by using JOINS costs 68%.

I still like to call SQL Server’s latest release by its code name “Denali”, not “SQL Server 2012”.

Stay tuned to other new features of Denali in my forthcomming posts.

For more information about the OVER() clause check MS BOL link here: http://msdn.microsoft.com/en-us/library/ms189461.aspx

Categories: SQL Server 2012, SQL Tips Tags: , , , ,