SQLAuthority News – Presented on Database Worst Practices at SQLPASS 2014 in Seattle – November 5, 2014

Earlier last month, I had the opportunity to present at SQLPASS 2014. The event was held in Seattle in November 2014. I had a great time attending this event and I presented on the topic Database Worst Practices on November 5, 2014.

Here is the title and abstract of the session, which I had presented at SQLPASS.

Secrets of SQL Server: Database Worst Practices

Abstract: 

“Oh my God! What did I do?” Chances are you have heard, or even uttered, this expression. This demo-oriented session will show many examples where database professionals were dumbfounded by their own mistakes, and could even bring back memories of your own early DBA days. The goal of this session is to expose the small details that can be dangerous to the production environment and SQL Server as a whole, as well as talk about worst practices and how to avoid them.

Shedding light on some of these perils and the tricks to avoid them may even save your current job. After attending this session, developers will only need 60 seconds to improve performance of their database server in their SharePoint implementation. We will have a quiz during the session to keep the conversation alive. Developers will walk out with scripts and knowledge that can be applied to their servers, immediately after the session.

The session was extremely well received as I had received wonderful feedback. I am honored that it was so well attended and the audience learned it. Here are a few photographs from the event. The session is recorded and available to SQLPASS subscribers who have paid for the event.

If you have not attended my previous session, you can subscribe to my newsletter over here, I will be sending various resources used in this sessions in emails.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

SQL SERVER – Reverse String Word By Word – Part 4

Earlier I wrote a blog post about SQL SERVER – Reverse String Word By Word. SQL Server Expert Sanjay Monpara and Yuri Petrov contributed to the blog post with their excellent script which I have blogged earlier. In this blog post we will see a similar script by SQL Expert Paul G. He has essentially sliced the string up by the passed delimiter (in this case space) and reconstructed the string the reverse order.

Here is the script by Paul:

DECLARE @String NVARCHAR(50)
DECLARE @Delimiter CHAR(1)
DECLARE @count INT
DECLARE
@slice VARCHAR(8000)
DECLARE @rtnString NVARCHAR(50)
SET @Delimiter = ' '
SET @String = 'I am Pinal Dave'
SET @rtnString = ''
SET @count = 1
WHILE @count > 0
BEGIN
SET
@count = CHARINDEX(@Delimiter,@String)
IF (@count > 0)
SET @slice = LEFT(@String,@count - 1)
ELSE
SET
@slice = @String
IF(LEN(@slice) > 0)
SET @rtnString = @slice + ' ' + @rtnString
SET @String = RIGHT(@String,LEN(@String) - @count)
END
SELECT
@rtnString

The above query will return results in the reverse order.

For example, as we have declared the variable as ‘My Name is Pinal Dave’, it will return results as ‘Dave Pinal is Name My’.

Thanks Paul for awesome contribution.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Creating the SSIS Catalog – Notes from the Field #058

[Notes from Pinal]: SSIS is very well explored subject, however, there are so many interesting elements when we read, we learn something new. A similar concept has been Creating the SSIS catalog.

Linchpin People are database coaches and wellness experts for a data driven world. In this 58th episode of the Notes from the Fields series database expert Tim Mitchell (partner at Linchpin People) shares very interesting conversation related to how to Creating the SSIS Catalog.


With the release of SQL Server 2012, Integration Services (SSIS) received a significant overhaul.  The most notable change was the addition of the SSIS catalog, a new deployment and storage mechanism that helps to centralize and standardize the storage, execution, and logging of SSIS packages.  In this post, I will briefly illustrate how to set up the SSIS catalog on a new installation of SQL Server 2012 or 2014.

The SSIS Catalog

When connecting to an instance of SQL Server using SSMS 2012 or 2014, you’ll notice that there is a new node in the tree, entitled Integration Services Catalogs.  By default, this node will be empty as shown below.

Before we can begin deploying SSIS packages to the catalog on this instance, we need to create the catalog.  It is worth noting here that, although the plural node name of Integration Services Catalogs implies that we may have more than one catalog, we can only create one SSIS catalog per SQL Server instance.

Creating the SSIS Catalog

To create the SSIS catalog, simply right click the Integration Services Catalogs node and choose Create Catalog.

Next, you’ll be presented with the Create Catalog dialog, which allows you to set the options for creating the catalog.  There are really only two things that you need to configure here:

  • Enable automatic execution of Integration Services stored procedure at SQL Server startup. This option will enable the automatic execution of a cleanup stored procedure when SQL Server starts.  This stored procedure will check for and update any packages whose status in the catalog was left in limbo due to an unexpected shutdown of SQL Server.  I don’t know of any downsides to enabling this, so I recommend that you leave this box checked.
  • Encryption password. Because you can store sensitive information (such as database or FTP passwords) in the SSIS catalog, this database requires an encryption password to properly secure those assets.

You’ll also notice that the name of the catalog database (SSISDB) is presented in this window, but it is not editable.  In the current versions (2012/2014) of SSIS, the name of the catalog database must be set to SSISDB.  Also, the SSIS catalog requires enabling CLR integration, so this option is shown but is also not editable.

Once you’ve configured the encryption password and selected the option of whether to allow the cleanup stored procedure to run, you’re ready to create the catalog.  Simply click the OK button in this window to create the catalog.  After so doing, you can browse to the Object Explorer in SSMS and see that we now have the SSISDB catalog listed under the Integration Services Catalogs node.  The catalog is now ready for use!

One final note on the SSIS catalog: If you browse the list of databases after creating the catalog, you’ll notice that there is a new user database named SSISDB in the list of databases.  The SSIS catalog is contained in its own database on the SQL Server instance on which it is installed.  Unlike prior versions of SSIS which stored the SSIS assets in MSDB, the SSIS catalog uses a dedicated user database to better isolate the SSIS storage.

Conclusion

With a new installation of SQL Server 2012 or 2014, the SSIS catalog is not installed by default.  This brief post demonstrated how to easily and quickly set up the SSIS catalog on a new installation of SQL Server.

If you want me to take a look at your server and its settings, or if your server is facing any issue we can Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Reverse String Word By Word – Part 3

Earlier I wrote a blog post about SQL SERVER – Reverse String Word By Word. SQL Server Expert Sanjay Monpara wrote a great comment to the blog post where he has proposed alternate solution. Right after the solution of the Sanjay, another expert Yuri Petrov suggested a modification to the script. In this blog post we will be seeing the solution proposed by Yuri Petrov in this blog post.

Yuri’s script is in two parts.

Part 1: Create a auxiliary table with numbers.

CREATE TABLE Nums (num INT PRIMARY KEY);
WITH
cteNums8000
AS (
SELECT TOP (8000) ROW_NUMBER () OVER (ORDER BY (SELECT 0)) AS [num]
FROM sys.columns a
CROSS JOIN sys.columns b
)
INSERT INTO Nums
SELECT num FROM cteNums8000;
GO

Part 2: Script to Reverse String Words. If you want, you can convert this script to function afterwards.

DECLARE @ip VARCHAR (8000) = 'My Name is Pinal Dave';
WITH
cteSplit
AS (
SELECT SUBSTRING (@ip, num, CHARINDEX (' ', @ip + ' ', num) - num) AS [value], num
FROM Nums
WHERE num <= LEN (@ip)
AND
SUBSTRING (' ' + @ip, num, 1) = ' '
)
SELECT STUFF ((SELECT ' ' + value
FROM cteSplit
ORDER BY num DESC
FOR XML
path (''), TYPE).value ('.', 'varchar (8000)')
,
1, 1, '')

The above query will return results in the reverse order.

For example, as we have declared the variable as ‘My Name is Pinal Dave’, it will return results as ‘Dave Pinal is Name My’.

Thanks Yuri for awesome contribution.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Proving that the Source of the Problems aren’t Tied to the Database

I have always wondered how Sherlock Holmes cracked the toughest of mysteries every single time. This fascination always lead me to learn new techniques every single time. The backbone for every successful detective is to be logical in their approach while working on the case. While watching movies, I have always seen a twist in the plot always makes the movie interesting and we are glued to the seat. If there is no drama, high octane action, restrictions – the movie would be boring. These techniques apply to real life too.

Being an application developer can always be demanding. You never know what situations we will get pushed into the next day. Here is one such situation which was just like a detective work. Once I was called for performance tuning exercise of our application that was running healthy for more than a year. The distress call from the customer got me thinking. What could have gone wrong in the application? Why all of a sudden this is happening? Did we release any upgrade or update to the database in the recent past as release? None of these were making sense as I kept thinking about possible root cause analysis. As soon as I landed at the customer site, I starting my interrogation with the DBA.

Pinal: Hello Sir!

DBA: Hi Pinal. Nice to see you here. Thanks for making time.

Pinal: It is a pleasure. I am surprised I had to come in such a short notice. Something must be really wrong then.

DBA: Yes. Our application users are complaining of very bad performance of your application for the past 2 weeks.

Pinal: Was it running fine before that?

DBA: I think, it must have been. They haven’t complained about it for more than a year. This seems to be a recent phenomenon. I think the application data must have increased considerably for this behavior.

Pinal: Hmmm … How is it possible that the application data increased all of a sudden in the past 2 weeks?

DBA: I am not sure. You are the expert. I am just a DBA and not an application developer. Must be something in your code.

Pinal: Can I get a chance to see the server console once?

DBA: Well, you know we are very sensitive with data privacy. I can surely get you access to an SSMS console. That must not be a problem.

Pinal: I can’t see the server even once?

DBA: Wait, let me get you the network / systems admin for this. They must know a way out.

After sometime, I get a chance to meet the Systems admin and continue the conversation.

Pinal: Hi there!

Admin: Hi. How can I be of help?

Pinal: I was wondering, if I can get access to the SQL Server box for my application?

Admin: Well, it is against our policy to open up the server directly. But what do you want to do?

Pinal: Ok. I am not sure what is going wrong on your server. Our application was running fine till about 2 weeks back. And now users are complaining about slowness in performance. I wanted to know what has changed in this timeframe all of a sudden.

Admin: Oh yeah, I forgot to tell you. We are in the process of doing server consolidation and are moving lot of our workloads from Physical to Virtual environment. And I know, we did move your database into a virtual environment.

Pinal: Cool. Are the configurations same as you moved these servers?

Admin: We made sure they are identical. So please don’t worry Pinal. It is something about your application code mostly.

Pinal: hmmm … Sure let me check. Thanks for your time.

I have always been a believer, accept but validate. There is nothing that can confirm your hypothesis but by doing the analysis all by yourself. So I bid a goodbye to Systems Admin and started my conversation with the DBA. I turned around and asked – “What do you use to monitor your SQL Server environments my friend?” He suggested the use of Spotlight on SQL Server for SQL Server. I felt so relieved that they had something.

Spotlight on SQL Server for SQL Server to the rescue

Having some monitoring tool is essential when working with SQL Server. Though there are a number of them, I had had my opportunity to look at Spotlight on SQL Server in detail in the past. It surely has come a long way and has nifty features that are worth a note.

In our conversation above, I was sure there was something fishy about the configuration the Systems Admin have given to SQL Server and I wanted to understand what are the complete Hardware Configuration for our database server.

The first report I pulled out as part of my diagnosis was “Hardware Configurations”. Voila, I had my answers right in front of my eyes and I was delighted my work was complete in less than 15 mins within the organization.

In the previous configuration (physical configuration), they used an 8 Processor machine for the application’s database server. As part of the consolidation, the network team accidentally made it a single CPU machine and it deprived the application of essential CPU cycles. So in a single click I was able to get the answers. I didn’t look further because all the physical hardware configurations were in black-and-white in this dashboard for me.

In fact, if we use the SQL Server Management studio’s dashboard we can get limited information. But nothing as exhaustive as what I got to see as part of data collection on Spotlight on SQL Server. The Server Dashboard does have the “# of CPU’s” values embedded into the report.

Conclusion

A lot of times we get misdirected with the problem in hand. These take us into a spin of wrong diagnosis. Don’t always work on the symptoms of the problem. Try to find out all the data points that lead to the problem and work your way out in solving the same. In our above example, the problem was never about performance of our applications. It was about wrong configuration and wrong migration done by the network team. It is essential we are aware of these pitfalls.

Try Spotlight on SQL Server free for 30 days!

Spotlight on SQL Server continues to break new ground when it comes to providing DBAs with the ultimate in flexibility to suit their specific needs. Building on the success of the Wait Stat analysis workflow, Spotlight on SQL Server version 11.0 now includes multi-dimensional workload analysis to provide maximum insight into performance issues, an enhanced mobile capability to remotely diagnose issues directly from mobile phones and the introduction of SNMP traps. A fresh new look provides simple to use, powerful visualization to effectively monitor the health of your SQL Server environment.

If you’re looking for additional help on how to optimize your SQL Servers, Visit Dell Software’s SQL Server Resource Center for video, guides and other useful content.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Reverse String Word By Word – Part 2

Earlier I wrote a blog post about SQL SERVER – Reverse String Word By Word. SQL Server Expert Sanjay Monpara wrote a great comment to the blog post where he has proposed alternate solution. Right after the solution of the Sanjay, another expert Yuri Petrov suggested a modification to the script. I have taken in consideration both the comments and solution and build a more robust script over here.

Here is the script to create a function

CREATE FUNCTION [dbo].[Split] (@string AS VARCHAR(8000), @splitAt AS VARCHAR(8000))
RETURNS @strings TABLE (ID INT, Strings VARCHAR(8000))
AS
BEGIN
DECLARE
@splitLen AS INT
DECLARE
@index AS INT
DECLARE
@id AS INT
SET
@id = 0
SET @splitLen = LEN(@splitAt)
SET @splitAt = '%' + @splitAt + '%'
SET @index = PATINDEX(@splitAt, @string)
WHILE(@index > 0)
BEGIN
SET
@id = @id+1
INSERT INTO @strings VALUES(@id,SUBSTRING(@string, 1, @index-1))
SET @string = SUBSTRING(@string, @index + 1, LEN(@string))
SET @index = PATINDEX(@splitAt, @string)
END
IF
LEN(@string) > 0
BEGIN
SET
@id = @id+1
INSERT INTO @strings VALUES(@id, @string)
END
RETURN
END

Here is how you will use the function:

DECLARE @Input NVARCHAR(MAX)
SET @Input = 'This is Reverse String test'
DECLARE @Output NVARCHAR(MAX)
SET @Output = ''
SELECT @Output = strings + ' ' + @Output FROM dbo.split(@Input,' ')
ORDER BY ID
PRINT @Output

The above script will return results just as we have seen earlier blog post.

 

Thanks Sanjay for awesome contribution.

In addition to the above Yuri Petrov has also suggested another script which we will see in future blog posts.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Can Database Primary File Have Any Other Extention Than MDF

Here is interesting question I received yesterday. I personally think it is one of the most interesting questions I have received this month.

“Can Database Primary File Have Any Other Extention Than MDF?”

Before you read the answer – think for a moment.

Now read the answer in the next line.

Yes, Of course. MDF file extension of the primary data file of the database is just a normal practice, but essentially, you can use any other database file extension as well.

Here is an example where I am able to attach files with PDF extension to the database. Please note that this file was created by SQL Server earlier and was always used for primary data file.

USE [master]
GO
CREATE DATABASE [tests] ON
( FILENAME = N'D:\data\tests.pdf' ),
(
FILENAME = N'D:\data\tests_log.ldf' )
FOR ATTACH
GO

As you can see that other extensions are successfully restored to the database. If you want to practice this example, you can download this example by clicking over here.

Reference: Pinal Dave (http://blog.sqlauthority.com)