Community is Great

I have a great respect for so many who contribute to the community, without them I would often struggle in my role for sure.

When "strange events" happen in a busy production environment it can be quite daunting when it seems everyone around is expecting you to have the answer/solution at your finger tips.

I'm indebted to Paul White http://sqlblog.com/blogs/paul_white/default.aspx  in confirming I'd found a bug and doing all the hard work including raising a connect item https://connect.microsoft.com/SQLServer/feedback/details/797837/incorrect-results-partitioned-nonclustered-index-with-descending-key

Let me explain: Somewhile back we had an issue with a report which essentially every so often would not return a correct result set. There didn't seem to be a particular pattern and the sql statement involved was very large and complex.

It appeared to be related to a secondary index sort order, however there wasn't time to investigate fully as the business was crying out for a "fix" and the developers provided such by rewriting the queries. On Friday last week the same sort of issue arose again, however this time the stored procedure was small containing only a handful of statements. Essentially the report worked on one day of the week but the following day it did not.

To test this I restored historic backups and showed that although it worked on Thursday it didn't work on Wednesdy, only 2 rows returned instead of 8 - as I said a simple report for once. To cut a long story short the culprit was a single column select from a two table join - one table was partitioned. It was easy to test this query and typically it returned 2 rows when called in the procedure but 6 rows when the isolated statement was called in SSMS.

There were a number of variations that I worked through including forcing joins which changed the results, I was able to test this against SQL 2012 as well as SQL 2008 which is what this production system uses.

I've always been a big fan of Paul's work so I dropped him a mail asking if he could have a look at my problem, I'd figured out that a fix was to change the sort order of an index and was under pressure to apply this. I was concerned I had found a bug and if so what other issues we might have and if changing the index sort order might cause other problems, I'm talking a billion+ GBP billing system here, so only slight pressure < grin >

You can read what Paul found and figured out in his blog and the connect - absolutely awesome and I want to publicly offer sincere thanks for all his hard work.

We will attempt to raise this through our corporate support to add weight to the connect.

As a footnote to this post as essentially it relates to Partitioning I have discovered issues if you make the partitioning index descending, I'll have to properly check this out and blog in detail. 

 

 

Posted by GrumpyOldDBA with no comments

Not obvious then ?

I requested that we service pack a third party app - here's the response

I have received a reply back from Engineering that SP4 is 
certified now with WLE 7.x. 
Engineering ran some tests against SP4 and it runs fine and 
doesn't introduce any obvious problems.
Posted by GrumpyOldDBA with no comments
Filed under:

You couldn't write it - Expired SA account

This is the stuff of DBA nightmares !

email trail:

Q. Can you reset the SA account on server XXXXX, we think it has expired and now no-one can work.

Connect to Server:
Surely no-one would set up a Server with an sa account which expires?
Thankfully not.
Find sa password and change connection to use SA account.
Connect without issue.

Me. Have checked Server and account is fine.

A. Thanks that's great, you've fixed it we can all work now.

Posted by GrumpyOldDBA with no comments
Filed under:

You Couldn't Write it - Houston we have a problem!

Note identities changed to protect the innocent (sic ).

In a datacentre I have an iscsi san which provides storage for a SQL Cluster.
It developed a fault and required replacement of a few parts, all hot swappable.
Although we had suppport/warranty this did not include onsite so we arranged to have the parts delivered.
The datacentre did not want to carry out the work so we had to arrange for the manufacturer to send an engineer.
Times were arranged and interested/concerned parties put on standby.
However it appears that somehow the storage was registered at a different location to its physical location, a mere 1,000 miles apart!So the engineer turned up at the wrong location, ironically Houston.

There is a postscript to this however, when we actually managed to get all the interested parties in the correct location, replacing the hot swap parts caused the cluster to fail over.

You Couldn’t Write it !! ( part 1 )

This post was inspired by a developer and I think illustrates the gulf that can sometimes exist between IT and the business.
I should point out that this post is the diplomatic version!

Initially I was sent a simple search for a person with a question about why the query plan showed a sort when there was no sort in the query and why did the sort show it was 40% of the query.
( The point about the sort belongs to another post some time. )

Easy answer to the duration was that this was a leading wild card search, so I decided to remove most of the query and just concentrate on the table containing the peoples names we were searching on.
My production server isn't a shoddy bit of kit so in the interests of science I performed the cut down query on the actual server.

Even with the all the required data in cache it took over 3 seconds to return a single name.


Now that's not very impressive to my mind,there's only a few million rows in the table and I'm searching one single column in memory which is indexed so no lookups are required, yes it has to scan but it’s in memory for goodness sake!
Well that clearly provided the evidence for why the search was so slow, I couldn’t initially just turn round and say it was the leading wild card I have to show it is, sigh!
So what happens if we drop the leading wild card but leave the trailing wild card?  Answer - so quick I had to use profiler to get a time, something under 100 ms.

Some time later in one of our UAT environments there were similar issues, our test systems don't have the resource of production so you expect some degradation.
I asked the developers how these searches were presented to the users, just to add to the confusion we have several interfaces or applications which front the same database.
In one application the default search is "Begins with ... "  e.g.  trailing wild card, alternative is "Contains...." leading and trailing wild card.
In the other main application default is "Contains..." with alternative of "Exact Match ... " , so most users choose "Contains..." regardless.

So there's a simple solution here, the same users can use both systems let's make them the same and we'll get a definite improvement.
The solution was put to the business - there was a very quick response - "Please make all searches "Contains.." and remove other options.”

Posted by GrumpyOldDBA with no comments
Filed under:

Performance issues due to Inactive Terminal Server Ports

This is an interesting post and although I don't have any direct issues of performance with any of my Servers I can see the build up in the registry as described.

If you make extensive use of DPR / TS sessions you might want to check this out

http://blogs.technet.com/b/askperf/archive/2012/03/06/performance-issues-due-to-inactive-terminal-server-ports.aspx

Posted by GrumpyOldDBA with no comments

We all read the instructions first - right ?

I've been working with the bare metal Hyper V and it's been an interesting time, if you haven't ever encountered windows core then it's a bit of a culture shock, as I understand it SQL Server will be running on windows core very soon - or maybe I read it wrong?

  Anyway having built my Hyper V server I decided that I'd build a cluster on it, for this you need some shared storage and one way to achieve this is to use Windows Storage Server which in effect creates an iscsi san.

So I downloaded Storage Server, which is an iso image, moved it to the Hyper V box with the intention of building a VM from the iso image ( you can do this and it is stunningly fast with windows 2008 R2 - but appallingly slow for XP Now I assumed as it was an iso it was a Server Install - but no it's not.

So I unpacked the iso and lo and behold there was an exe. So I built a w2k8 r2 VM and ran the exe - which promptly created two iso images - now I'm getting confused!

Back to building a server from the iso image ( again ) - yet another failure.

OK unpack the iso and we have a bunch of files - this isn't a bootable image or anything.

  So in a nut shell the iso image holds an exe file which holds an iso image which holds a bunch of files - why not just zip the lot up together Microsoft? talk about making life hard! 

That was probably the most confusing part of building a cluster - I might suggest that some pictures in the docs would have made life easier. It assumes, I guess, that you know all about isci targets and such, however there's only so many variations and I essentially knew what I should end up with, once I figured out that the cluster was built very quickly. 

Am I lover of virtualisation? Not really, my experience is that SQL Server performance sucks under virtualisation, however I love Microsoft because without them I don't earn a living, so when Microsoft made the bare metal hypervisor available I thought I'd take in some training and investigate.

So I have a DL585 configured and as well as the cluster I have SQL2012 and windows 8 VMs to evaluate. 

http://www.microsoft.com/en-us/server-cloud/windows-server/hyper-v.aspx

x64 Memory Issues - revisited

It's funny how things seem to bob to the surface every so often, a bit like revivals of Musicals or the fact you know it's christmas becuase ET is on the TV again.

Blake Morrision from the Ask the Performance Team has blogged about issues with working sets and SQL Server, http://blogs.technet.com/b/askperf/archive/2012/01/25/real-life-sql-working-set-trimming-issue.aspx

( If you don't subscribe to this blog then you're missing a useful source of help )

I blogged about this back in March 2009  http://sqlblogcasts.com/blogs/grumpyolddba/archive/2009/03/18/x64-memory-problems.aspx and at the time deployed the dynamic cache service to deal with this exact problem.

Personally given the circumstances I'd never allow a third party backup tool onto a production SQL Server, my preference is to ship or replicate backups to another location where the streaming to tape can occur without any impact on the Production Server.

I also don't slavishly apply lock pages in memory, my view is that if you have to set this to stop memory paging it would be a far better option to find the root cause and fix that rather than sweeping it under the table - no doubt many will not agree with me on this!

Q. When is a HEAP not a HEAP ?

A. When it’s on SQL Server.

  A very important part of Business Analysis is understanding the perceptions of users even if they haven’t clearly stated a requirement, so a SQL Server table without a clustered index is known as a heap ( in fairness I could say that a table with a clustered index is an ordered heap ), programmers understand heaps as a last in first out “store” ( LIFO ) so if I posed this question

  " If I put 200 rows of data into a SQL Server table which has no indexes and then select a top 100 * would you expect the first 100 rows or last 100 rows to be returned?"

  If you think HEAP then your answer probably will be " the last 100 rows " ( LIFO ) ( this was the answer I received from those I asked )

  However in my tests with SQL Server 2008 and SQL Server 2012 the results returned are always the first 100 rows. This must prove that even a HEAP follows the default ASC order which is by default applied to every index created, it also shows that you cannot actually implement a heap process without using an order by – which is annoying to say the least. There’s also some similar ( ish ) behaviour with partitioned tables where a top on a desc clustered key actually returns asc data, or in some cases a semi random set of data, however I haven’t finished my tests on SQL 2012 yet so I’ll post about this later.

  It’s purely of incidental interest of course but the fact remains you don’t always want to have sorts being carried out on data which is already ordered and if your table is a heap then I could say it should behave like one.

  Here’s a dictionary definition for HEAP "a group of things placed, thrown, or lying one on another"

 If you consult Wikipedia concerning LIFO it says this belongs to a stack.

 Here’s a dictionary definition for stack "a more or less orderly pile or heap"

( tried to sort out the formatting - sorry - what you see isn't always what you get! )

Posted by GrumpyOldDBA with no comments
Filed under:

A follow up to yesterday

As I have been asked,  here to tidy up yesterdays post is the procedure my startup procedure calls along with the logging table deployed in the DBA database.

Just to muddy the water further I have routines for remotely calling the DBAMessages table through a remote server to send out email from a central server!!

Just to explain that I have been ( previously ) limited to only using one Server to send email alerts for multiple Servers so I attempt to code to deal with all possible circumstances.

I have two DBA databases on each server which hold code and data I use to manage the server.
I try to make sure everything is generic rather than specific as carrying a different version of a procedure or table on every server can quickly become a nightmare, not counting anything  specific to the SQL Server version or edition.

Here’s the procedure which handles messages and the table that messages are written to ( optionally ).

/****** Object:  StoredProcedure [dbo].[dbasp_SendMessage]    Script Date: 11/21/2011 19:53:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE proc [dbo].[dbasp_SendMessage]
@subject varchar(100),
@message varchar(1000),
@to varchar(255)
-- =============================================================  
--  Stored Procedure:     dbo.dbasp_SendMessage                          
--  Written by:         Colin Leversuch-Roberts
--                      www.kelemconsulting.co.uk     
--                        (c) 2005                            
--                                                                
--  Purpose:               simplifies sending a message to email or file   
--                                                                
--  System:                DBA maintenance - ServerAdmin
--
--  Input Paramters:        @subject   -  the email/message subject
--                                     @message   -  email/message body
--                                     @to        -  recipients
--             
--  Output Parameters:     None                                      
--  Return Status:         @@error
--                                                                
--  Usage:                 EXEC dbo.dbasp_SendMessage 'Warning','message','fred@somedomain.co.uk'
--                                                                
--  Called By:                                                           
--  Calls:                 DBADatabase.dbo.dbasp_SendSMTPmail
--                        DBADatabase.dbo.ServerParameters ( table )
--                      DBADatabase.dbo.DbaMessages ( table )
--
--  Notes:        
--            This is to simplify the direction of messages if a mail server
--            is unavailable. Sends mail or writes to message table
--
--                                                                
--  VERSION HISTORY
--  Version No        Date            Description
--  1            19th May 2005    Initial Release
--  2                            sql 2008
-- =============================================================  
--
as
set nocount on
declare @from varchar(100) = @@servername+'@regus.com';
--
IF (Select ParmValue from DBADatabase.dbo.ServerParameters where ParmName = 'MessageMail')=1
    BEGIN
        exec DBADatabase.dbo.dbasp_SendSMTPmail @from, @to, @subject, @message;
        print  @subject;
    END
--endif
IF (Select ParmValue from DBADatabase.dbo.ServerParameters where ParmName = 'MessageLog')=1
    BEGIN
        Insert into DBADatabase.dbo.DbaMessages(Alert,[Notification]) values (@subject,@message);
        print  @subject;
    END
--endif
return(@@error);
GO

And this is the table definition

/****** Object:  Table [dbo].[DbaMessages]    Script Date: 11/21/2011 20:01:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DbaMessages](
    [Numkey] [int] IDENTITY(1,1) NOT NULL,
    [TheDate] [datetime] NOT NULL,
    [Alert] [varchar](250) NOT NULL,
    [Notification] [varchar](5000) NOT NULL,
    [Actioned] [tinyint] NOT NULL,
 CONSTRAINT [PK_DbaMessages] PRIMARY KEY CLUSTERED 
(
    [Numkey] DESC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF, FILLFACTOR = 100) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING ON
GO
ALTER TABLE [dbo].[DbaMessages] ADD  CONSTRAINT [DF_DbaMessages_TheDate]  DEFAULT (getdate()) FOR [TheDate]
GO
ALTER TABLE [dbo].[DbaMessages] ADD  CONSTRAINT [DF_DbaMessages_Actioned]  DEFAULT ((0)) FOR [Actioned]
GO
More Posts Next page »