• About

sqlerrors

~ From the errors of others, a wise man corrects his own

sqlerrors

Monthly Archives: November 2013

find Enterprise-only features in your database

29 Friday Nov 2013

Posted by Praveen D'sa in SQL Server

≈ Leave a comment

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.

Advertisement

Share this:

  • More
  • Email
  • Print

Like this:

Like Loading...

Find Windows and SQL Server Last Restart time

26 Tuesday Nov 2013

Posted by Praveen D'sa in SQL Server

≈ Leave a comment

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)'
sp_readerrorlog

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.

dashboard_report

Method 6: 

start -> run -> eventvwr

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

Here is the output of above filter.
eventviewer_result

 

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.
taskmanager

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.

server_restart

Here is the output of above filter.
server_restart_result

Hope this helps!

Share this:

  • More
  • Email
  • Print

Like this:

Like Loading...

sp_helptext to view objects on a linked server

20 Wednesday Nov 2013

Posted by Praveen D'sa in SQL Script

≈ Leave a comment

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!

Share this:

  • More
  • Email
  • Print

Like this:

Like Loading...

questionário

19 Tuesday Nov 2013

Posted by Praveen D'sa in Uncategorized

≈ Leave a comment

Share this:

  • More
  • Email
  • Print

Like this:

Like Loading...

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

14 Thursday Nov 2013

Posted by Praveen D'sa in SQL Script, SQL Server

≈ 1 Comment

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
first and last day of month - sql server

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.

Share this:

  • More
  • Email
  • Print

Like this:

Like Loading...

SERVERPROPERTY – one stop shop for all SQL Server properties.

12 Tuesday Nov 2013

Posted by Praveen D'sa in SQL Server

≈ Leave a comment

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
serverproperty

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

Share this:

  • More
  • Email
  • Print

Like this:

Like Loading...

Learn MS SQL Server throgh Videos

08 Friday Nov 2013

Posted by Praveen D'sa in Learning

≈ Leave a comment

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.

Share this:

  • More
  • Email
  • Print

Like this:

Like Loading...

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

07 Thursday Nov 2013

Posted by Praveen D'sa in Learning

≈ Leave a comment

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“

Share this:

  • More
  • Email
  • Print

Like this:

Like Loading...

Analyzing IO subsystem latencies from SQL server

05 Tuesday Nov 2013

Posted by Praveen D'sa in Performance Tuning

≈ Leave a comment

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

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.

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/

Share this:

  • More
  • Email
  • Print

Like this:

Like Loading...

Recent Posts

  • What’s wrong with this query? July 30, 2015

Categories

  • Learning (2)
  • MS SQL Tips (3)
  • Performance Tuning (1)
  • SQL Script (5)
  • SQL Server (14)
    • Index (1)
    • Log Shipping (1)
  • SQL Tips & Tricks (1)
  • Uncategorized (5)

Archives

  • July 2015 (2)
  • February 2015 (1)
  • October 2014 (2)
  • September 2014 (1)
  • May 2014 (1)
  • April 2014 (3)
  • March 2014 (2)
  • February 2014 (2)
  • January 2014 (1)
  • December 2013 (3)
  • November 2013 (9)

sqlerrors

sqlerrors

Disclaimer:

This is my personal blog. The opinions & suggestions expressed here are
my own. For accuracy and official references please refer to MSDN,Microsoft TechNet,
Books Online. I do not endorse any of the tools / applications / books / concepts
mentioned here on my blog. I have simply documented my personal experiences on this
blog.

Create a free website or blog at WordPress.com.

Privacy & Cookies: This site uses cookies. By continuing to use this website, you agree to their use.
To find out more, including how to control cookies, see here: Cookie Policy
  • Follow Following
    • sqlerrors
    • Already have a WordPress.com account? Log in now.
    • sqlerrors
    • Customize
    • Follow Following
    • Sign up
    • Log in
    • Report this content
    • View site in Reader
    • Manage subscriptions
    • Collapse this bar
 

Loading Comments...
 

    %d bloggers like this: