find Enterprise-only features in your database

Tags

DMV, enterprise edition, Enterprise feature, enterprise license, enterprise only features, sys.dm_db_persisted_sku_features

I have SQL Server that was installed as enterprise edition, we have never utilized the enterprise-only features on this particular server and want to downgrade to Standard edition so that we can make use of this enterprise license in some other server.
There’s an easy way to tell whether the database contains enterprise-only features. In SQL Server 2008 onwards a new DMV sys.dm_db_persisted_sku_features has been added that will report you which enterprise only features are present in a database. Let’s check it out.

Method 1:
You need to run below query on every database to check for enterprise-only features.

SELECT * FROM sys.dm_db_persisted_sku_features

Method 2:
The following query will report all enterprise-only features from all databases.

Declare @EntFeatures Table(DbName NVARCHAR(255),[Enterprise feature] NVARCHAR(255))
INSERT INTO @EntFeatures
exec sp_msforeachdb 'select "?" AS DatabaseNames,feature_name from [?].sys.dm_db_persisted_sku_features'
SELECT * FROM @EntFeatures

Method 3:
The following query makes use of cursor to achieve the same result as in method 2.

Declare @dbid Int
Declare @STR Varchar(100)
Declare @EntFeatures Table(DbName NVARCHAR(255),[Enterprise feature] NVARCHAR(255))
Declare rs scroll cursor for
Select database_id From sys.databases
open rs
fetch first from rs into @dbid
while @@fetch_status= 0
Begin
Select @STR = 'use ' + db_name (@dbid) +
CHAR(13) + ' SELECT db_name(),feature_name FROM sys.dm_db_persisted_sku_features'
Insert Into @EntFeatures
EXEC ( @STR )
fetch next from rs into @dbid 
End 
Close rs 
Deallocate rs 
Select * from @EntFeatures

sys.dm_db_persisted_sku_features DMV only reports below four features.

• Data compression
• Partitioning
• Transparent data encryption
• Change data capture

As per MSDN Library even Database Snapshot & Online Indexing are enterprise-only features , if you have enabled these features in your database it will not be reported by this DMV.
To make sure I created a database snapshot and ran this DMV but it did not report database snapshot as enterprise-only feature same is with online indexing which I enabled for one of my maintenance plans but DMV again missed out this as well.

Find Windows and SQL Server Last Restart time

Tags

sp_readerrorlog, SQL DMV, SQL Server, SQL Server last restart, sql server restart

As a routine DBA activity I was analyzing sp_readerrorlog this morning and surprised to see that SQL Server has restarted early this morning.

Here are few different ways that you can make use of to tell when SQL Server was last restarted.

Method 1: 

sp_readerrorlog 0,1,'Copyright (c)'

Method 2:

SELECT sqlserver_start_time FROM sys.dm_os_sys_info

sqlserver_start_time
———————–
2013-11-26 01:16:10.757

(1 row(s) affected)

Method 3:

SELECT login_time FROM sys.dm_exec_sessions WHERE session_id = 1

login_time
———————–
2013-11-26 01:16:24.127

(1 row(s) affected)

Method 4: 

SELECT create_date FROM sys.databases WHERE name = 'tempdb'

create_date
———————–
2013-11-26 01:16:13.023

(1 row(s) affected)

Method 5:

Right click on the server name in SSMS and select Reports > Standard Reports > Server Dashboard and you will get a report similar to below report.

Method 6: 

start -> run -> eventvwr

Chose Windows log -> Application
Open filter and enter Event Sources (MSSQLSERVER) and Event Id (17163)

Here is the output of above filter.

 

Here are a few different ways that you can tell when Windows last rebooted.

Method 1:

Start Task Manager->Performance
Screen below gives you total up time since last restart.

Method 2:

start -> run -> eventvwr
Choose: Windows Log – > System
Open filter and following Event Id’s
6005 to see when the Event Log service was started. It gives the message “The Event log service was started“.
6006 to see when there was a clean shutdown. It gives the message “The Event log service was stopped“.
6008 to see when there was a dirty shutdown. It gives the message “The previous system shutdown at time on date was unexpected“.
6009 is logged during every boot.

Here is the output of above filter.

Hope this helps!

sp_helptext to view objects on a linked server

Tags

linkedserver, remote sp_helptext, source server, sp_help, sp_helptext

Access object definition of linked server from source server.

Syntax to execute sp_Helptext on Linked server
EXEC [LINKEDSERVER].[DATABASE].dbo.sp_Helptext ‘OBJECTNAME‘
Object name could be procedure,view

Syntax to Execute sp_Help on Linked server
EXEC [LINKEDSERVER].[DATABASE].dbo.sp_Help ‘OBJECTNAME‘
Obejct name could be table, procedure, view

Hope this helps!

questionário

SQL Server – Query to find first and last day of any month

Tags

dateadd, datepart, datetime, first and last day of the month in sql, sql datetime, SQL Server datetime

Following query will take date as a input parameter and  returns you first and last days of previous month, current month and next month.

SET NOCOUNT ON
DECLARE @MonthStartDate As datetime
DECLARE @MonthEndDate As datetime
DECLARE @today datetime
SELECT @today = convert(varchar(12) , getdate() , 101) 
--change date as per your requirement

--First & Last dates for previous month
SET @MonthStartDate = @today
SELECT @MonthStartDate = dateadd ( dd , - datepart ( dd , @MonthStartDate ) , @MonthStartDate+1 )
SELECT @MonthStartDate = dateadd(mm , -1 , @MonthStartDate)
SELECT @MonthEndDate = dateadd(dd , -1 , dateadd ( mm , +1 , @MonthStartDate))
SELECT @MonthStartDate AS StartDate , @MonthEndDate AS EndDate

--First & Last dates for current month
SET @MonthStartDate = @today
SELECT @MonthStartDate = dateadd ( dd , - datepart ( dd , @MonthStartDate ) , @MonthStartDate+1 )
SELECT @MonthEndDate = dateadd(dd , -1 , dateadd ( mm , +1 , @MonthStartDate))
SELECT @MonthStartDate AS StartDate , @MonthEndDate AS EndDate

--First & Last dates for next month
SET @MonthStartDate = @today
SELECT @MonthStartDate = dateadd ( dd , - datepart ( dd , @MonthStartDate ) , @MonthStartDate+1 )
SELECT @MonthStartDate = dateadd(mm , +1 , @MonthStartDate)
SELECT @MonthEndDate = dateadd(dd , -1 , dateadd ( mm , +1 , @MonthStartDate))
SELECT @MonthStartDate AS StartDate , @MonthEndDate AS EndDate

Result

Use Convert function to format the out put.
convert(varchar(12) , getdate() , 101) — MM/dd/yyyy
convert(varchar(12) , getdate() , 103) — dd/MM/yyyy

Happy learning.

SERVERPROPERTY – one stop shop for all SQL Server properties.

Tags

Microsoft SQL Server 2005, SERVERPROPERTY, SQL Server, SQL Server 2005

SQL Server has little known yet powerful function called SERVERPROPERTY which allows you to extract tremendous amount of information about your SQL Server.

Syntax
SERVERPROPERTY(propertyname)

Below are some example of SERVERPROPERTY.

SELECT 
SERVERPROPERTY('Edition') AS Edition ,
SERVERPROPERTY('ProductLevel') AS ProductLevel,
SERVERPROPERTY('ProductVersion')AS ProductVersion ,
SERVERPROPERTY('Collation') AS EngineEdition ,
SERVERPROPERTY('IsFullTextInstalled') AS IsFullTextInstalled ,
SERVERPROPERTY('IsSingleUser') AS IsSingleUser ,
SERVERPROPERTY('MachineName') AS MachineName

Result

Check here for full list of parameters.

Why SERVERPROPERTY Why not @@VERSION
Most of the time I have seen DBA’s using @@VERSION  to extract SQL Server properties, but be aware that in some cases using @@VERSION would mislead you, take a below example.
If you run @@VERSION on SQL Server 2005.

SELECT @@VERSION

Result
Microsoft SQL Server 2005 – 9.00.5000.00 (X64)
Dec 10 2010 10:38:40
Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1
(Build 7601: Service Pack 1)

Here Service pack 1 is of operating system not of SQL Server,instead you can make use of SERVERPROPERTY to identify service pack for SQL Server.

SELECT SERVERPROPERTY (‘ProductLevel‘)

Result
SP4

This issues is been fixed in SQL Server 2008 onwards, 2008 onwards @@VERSION returns SP of both SQL and OS.

SELECT @@VERSION

Result
Microsoft SQL Server 2008 R2 (SP2) – 10.50.4000.0 (X64)
Jun 28 2012 08:36:30
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.1
(Build 7601: Service Pack 1)

New Parameters
In SQL Server 2012 two new parameters are added to SERVERPROPERTY function namely InstanceDefaultDataPath and InstanceDefaultLogPath.
InstanceDefaultDataPath would return you default data directory and InstanceDefaultLogPath would return you log directory.

Hope this helps u!
Praveen

Learn MS SQL Server throgh Videos

Tags

sql errors, sql videos, sqlerrors, video instruction, video instructions, Video tutorials

Ours is a much more visual generation that just might enjoy an video instructions than a write-up or read. Video tutorials not only educate you but also entertain.
Advantage of video instruction over text is that you have a view what actually being done one the screen, with text it is easy to get lost.

So, if you like to learn MS Sql server though video tutorials, check out these free links.

Free Online SQL Server MCM / MCSM Training Videos

Free SQL Server Video Tutorials and Training

Microsoft Virtual Academy

i’ll update this list whenever i find new resources, till then happy learing.

If you stop learning in life, you will stop winning.

Tags

Dr. APJ Abdul Kalam, learning, learning is a continuous process, Microsoft, Microsoft Virtual Academy, MVA, sql errors, sqlerrors, virtual academy

I would remember a great quote from Dr. APJ Abdul Kalam on learning “Learning gives creativity, creativity leads to thinking, thinking provides knowledge, knowledge makes you great”. Learning is a continuous process, if we stop learning and thinking, then there is no creativity and knowledge in our life.

If you are IT professionals especially working with MS technologies keeping up with changes is quite painful, to remain competitive we have no option but to update ourselves.

Recently Microsoft has come up with Microsoft Virtual Academy (MVA), Microsoft Virtual Academy (MVA) offers free online Microsoft training delivered by experts to help technologists continually learn, with hundreds of courses across Windows Server 2012, Windows 8, virtualization, app development for HTML5, Windows and Windows Phone, Microsoft Office 365, SQL Server, Azure, and System Center.

MVA offers various ways to learn, such as watching a video, downloading a document, completing a self-assessment or attending a live event, once you register yourself you can earn different level of badges also get certificates on completion of courses.

Visit MVA home page for more information.

Don’t learn because “You think you should learn” learn because “You want to learn“

Analyzing IO subsystem latencies from SQL server

sqlerrors

Issue:

We have a business critical server running MS SQL Server 2008 R2 Enterprise on Windows 2008 R2 Enterprise, in the recent past users have started complaining about performance issues, several transactions & reports started taking time to execute, IT manager has asked us to analyze and fix sudden degradation of performance.

Analysis:

We started with IOPS analysis on SAN Storage, IOPS report surprised all of us, as IOPS not even hitting 50% of max allowed threshold. Then I started tuning indexes, dropping unwanted indexes (we have weekly maintenance job for index rebuild/reorganize) but no luck no improvement in performance to further investigate the issue I ran profiler with various counters but could not manage to get single conclusive evidence. At last I turned to my most trusted & reliable friend GOOGLE, and I found loads of stuff on performance tuning which I had already tried, at last I found one good article on examine IO subsystem latencies from within SQL Server by Paul Randal. Analyzing IO subsystem latencies from SQL server through sys.dm_io_virtual_file_stats DMV

SELECT
–virtual file latency
[num_of_writes] , [num_of_reads] , [ReadLatency] = CASE WHEN [num_of_reads] = 0 THEN 0 ELSE ([io_stall_read_ms] / [num_of_reads]) END, [WriteLatency] = CASE WHEN [num_of_writes] = 0 THEN 0 ELSE ([io_stall_write_ms] / [num_of_writes]) END, [Latency] = CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0) THEN 0 ELSE ([io_stall] / ([num_of_reads] + [num_of_writes])) END,
–avg bytes per IOP
[AvgBPerRead] = CASE WHEN [num_of_reads] = 0 THEN 0 ELSE ([num_of_bytes_read] / [num_of_reads]) END, [AvgBPerWrite] = CASE WHEN [io_stall_write_ms] = 0 THEN 0 ELSE ([num_of_bytes_written] / [num_of_writes]) END, [AvgBPerTransfer] = CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0) THEN 0 ELSE (([num_of_bytes_read] + [num_of_bytes_written]) /([num_of_reads] + [num_of_writes])) END, LEFT ([mf].[physical_name], 1) AS [Drive], DB_NAME ([vfs].[database_id]) AS [DB], [mf].[physical_name] FROM sys.dm_io_virtual_file_stats (NULL,NULL) AS [vfs] JOIN sys.master_files AS [mf] ON [vfs].[database_id] = [mf].[database_id] AND [vfs].[file_id] = [mf].[file_id] ORDER BY LEFT([mf].[physical_name] , 1) ASC;

This query allows me quickly analyze where read and write hot spots are and also enables me to go to particular database files to see what’s going on and helps me to decide do I have to move these files to dedicated or faster RAID groups.

sample result set

As you can see query result, one of tempdb files Write latency is bit high, similar issue with other database file groups in many cases read/write latency is more than 100 ms, in case of J drive read latency is 745 ms and write latency is 454 ms.

read write latency on different drives.

Conclusion: Question: what is the acceptable latency on production server? Well it depends on the criticality of the application it is running along with RAID groups and technology of the drives (SCSI or SATA or SSD).Keeping all these things in mind we have redesigned our new SAN storage, we have included SSD to host tempdb, RAID 10 to host write intensive file groups and RAID 5 for read intensive file groups along with 100 GB SSD fast cache at storage level. We will be deploying this new storage in a month and I will update you with new result post deployment to compare the performance improvement through new SAN implementation.
References:
http://www.sqlskills.com/blogs/paul/how-to-examine-io-subsystem-latencies-from-within-sql-server/