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

Hello it’s your server calling

This is nothing exciting but I've always found this startup procedure  very useful.
All this simple procedure does is send you an email if the SQL Service Starts.
If your Server is a cluster it will tell you which node you're on.
--
On it's own this procedure can't actually be used as I route the output through another procedure, dbasp_SendMessage, this procedure routes a passed message to either a smtp email or a log table or both, the destination is set in a server config table which allows me to still log dba messages even if the mail server is unavailable or if I'm in a environment which doesn't have email.
I'm assuming that most have a favourite stored procedure which will send an smtp email so all you need to do is substitute the procedure.
For SQL 2005 you'll have to define and assign the variables on seperate lines.
--
All my SQL Jobs have customised failure/success alerting which is routed through dbasp_SendMessage, the dbasp_ prefix was accepted practice at one client and I have not chosen to change it.
--
I set all my alerts with the format of three exclamation marks either side of a key word, this assists with rules in Outlook, by default the sender address of the email is set to @@ServerName, this avoids any confusion with multiple environments raising alerts.

 

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[sp_Mail_DBA_on_Start]    Script Date: 11/21/2011 13:53:26 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Mail_DBA_on_Start]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_Mail_DBA_on_Start]
GO

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[sp_Mail_DBA_on_Start]    Script Date: 11/21/2011 13:53:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE procedure [dbo].[sp_Mail_DBA_on_Start]
-- ============================================================================
--  Stored Procedure:     sp_Mail_DBA_on_Start                          
--                       
--  Written by:     Colin Leversuch-Roberts
--                     www.kelemconsulting.co.uk 
--                    (c) january 2005                            
--                                                                
--  Purpose:          Nothing very complicated, uses  SMTP proc to mail the DBA group
--                    when the sql server restarts
--                    Master procs can be set to run on start-up, this is supported by Microsoft
--                    and is a valid implementation.
--                    set to autorun using exec sys.sp_procoption '[dbo].[sp_Mail_DBA_on_Start]','startup','true';
--                    check status using select OBJECTPROPERTYEX(  object_id('[dbo].[sp_Mail_DBA_on_Start]'),'ExecIsStartup');                            
--                                                                
--  System:            master database
--                    does not need to be marked as a system object
--
--  Input Paramters:       none
--                       
--  Output Parameters:     None                                      
--                                                                
--  Usage:                 runs on sql server service restart                                          
--                  
--  Calls:                 dbo.dbasp_SendMessage
--                                                           
--  Data Modifications: None                             
--                                                                
--  VERSION HISTORY
--  Version No        Date            Description
--  1            28-January-2005        Initial Release 
--    2           5 dec 2008            now sends message through  dbasp_SendMessage
--    3            jan 2011            changed to pick up physical name so names cluster node in message
-- ============================================================================
as
set nocount on;
declare @subject varchar(100) = '!!! ALERT !!! The SQL Service has just started';
declare @message varchar(1000) = 'The SQL Service has started on '+convert(varchar(100),SERVERPROPERTY('ComputerNamePhysicalNetBIOS'))+' - Time of event '+convert(varchar(30),getdate())+''+char(10)+char(10)+' If this was not planned then there may have been a server os failure or unauthorised reboot';
declare @to varchar(255) = 'DBAGroup@yourdomain.com';
exec ServerAdmin.dbo.dbasp_SendMessage @subject,@message,@to;
GO

EXEC sp_procoption N'[dbo].[sp_Mail_DBA_on_Start]', 'startup', '1'
GO
Posted by GrumpyOldDBA with no comments

Table Variables and Parallel Plans

I’m sure somewhere there must be carved into the side of a mountain those immortal words “ Well it seemed a good idea at the time!” ( with due homage to HHGTTG )

Hopefully there’s nothing in this post which you the reader will not know about already? However, I thought it would be interesting to recount the steps and findings from tuning a small stored procedure in one of our production systems as it illustrates how simple changes can improve performance and how two aspects of tuning can interact to degrade performance.  As you will probably have guessed from the title it’s Table Variables and ( the lack of ) Parallelism.

I’ve been pretty sharp over the years with anyone who has blogged about turning off parallelism, apart from a couple of isolated applications I have never turned off parallelism ( even with oltp type applications ).It’s quite difficult I think to place every application into either an oltp or dss silo, and as I’ve frequently said it’s no point having 80 cores on your server if you’re going to use it single threaded.

I can’t publish the code for this procedure, I don’t think it would make much sense anyway after I’ve tried to obscure what it does, but I will describe what it did. ( All the stats are from the output of profiler, the tests were run on a 16 core server, SQL 2008 Enterprise, all results are from in cache objects and data. )

Step 1. Insert into a table variable 3 integer values from a 3 table join with two not exists in the where clause which join to the source table(s).

Step 2. In a loop insert into the table variable based upon a join to another table ( 60 times only )

Step 3. Select the table variable joined to another table,grouped by one column value.

The output is about 80 rows of 2 integer values.

The procedure is called from another procedure as part of a bigger process. I was drawn to the calling procedure because it was slow running, up to 25 seconds.

The main procedure actually calls several other stored procedures, including this one, so I set a profiler to capture the duration and io of each statement, this showed that in my test calls to this procedure were responsible for almost all of the duration, but only 30% of the io.

After extracting some sets of typical parameters for this call I set about looking to see what the problem was.

My starting call ( a typical data set ) showed 9,673 cpu cycles, 266,000 page reads and a duration of just over 10 seconds.

The underlying tables are not vast, 4.5 and 17 million rows for the largest. There were no table scans and on the whole the queries were not too bad , these tables are quite wide and selections were generally on keys and foreign keys – I did note a couple of bookmark lookups.

One point to note here is that SSMS did not suggest any missing indexes which might improve performance.

Now table variables can be a pain, true they stop issues of recompilation, not so much an issue in SQL 2008, however just to recap, they don’t carry stats and they will not carry a parallel plan. Common myth is that table variables create in memory whilst #tables create on disk, it’s also a myth that putting a Primary Key on a table variable makes things better, it doesn’t, it will enforce uniqueness but there is only one operation on a table variable and it’s a full table scan.

My view is that you never want to join table variables to permanent tables as plans will most likely be sub optimal, I also don’t have much time for updates to table variables which use joins.

So first step ditch the table variable and put in a #table, here’s the result.

 

  cpu Page Reads Duration ( secs )

Table Variable

9,673

266,036

10.096

#Table

5,645

106,165

1.325

 

As you can see just replacing the table variable brings significant performance gains. I should point out that the results are a typical set which are representative, it’s not a worst and best.

At this point you might think well that’s fixed, what next? Aha not quite. Now freed from the table variable I had a parallel plan and a missing index suggested by SSMS. I checked out the suggestion and it was correct so I added it and went back to the plan – another index was suggested so I also added this. Results now for the two versions of the proc:

With first missing index

  cpu Page reads Duration ( secs )

Table Variable

1,747

207,163

2.267

#Table

2,541

47,311

1.293

With second missing index

  cpu Page reads Duration ( secs )

Table Variable

2,761

39,011

3.231

#Table

2,384

38,528

1.070

Checking the query plan I noted that the looping was performing a bookmark lookup, this only required an index on one column with an include on a second column, both integers. Adding this index gave me this result

  cpu Page reads Duration ( secs )

Table Variable

2,668

36,068

3.020

#Table

2,261

35,708

0.857

The initial population of the #table was generating a parallel plan, I have max degree of parallelism set to 8 on the server ( 16 cores ) so the next step was to see if if I could get any more performance by changing the level of parallelism

Maxdop Duration ( secs )

16

1.322

8

0.796

4

1.004

1

3.007

auto

1.001

Now I had the show execution plan checked during testing, it’s interesting to be able to note that there is indeed an overhead, which is clear in these results.

 

Maxdop Duration ( secs )

16

0.413

8

0.299

4

0.558

1

2.511

auto

0.455

However the answer in this case is that a definitive forced setting of 8 will bring the biggest benefit.

NB. by “auto” I mean the default setting of 8 on the server configuration.

I should point out that generally I attempt to avoid using direct hints as a tuning tool, it adds an extra level of complexity that requires constant checking for every service pack, the maxdop doesn’t map for our dev and test environments which don’t have as many cores, so in general it could lead to problems later. That said this procedure is key to a particular performance issue.

Final Note:  I don’t mean by this that all table variables should be replaced with #tables, however in a number of cases where table variables feature in say 4 table joins with proper tables, making the table variable a #table has given significant performance gains for me in application tuning. Ultimately if I can shave 10 or 20 seconds from an execution time then to me that’s a significant gain.

Posted by GrumpyOldDBA with no comments

Posts of Interest

I don't make a habit of just posting links as I think it's like a form of cheating, however sometimes there are things I want to keep track of and I know If I put the link on my blog I'll find it again!

so: a short one here;  We've just bought some new servers with 10 core processors, or 20 if you turn on hyperthreading. That's 80 cores/threads which is sort of a waste if you're not going to make use of parallel plans, however it does make a backup fast!  I saw this post form the CSS team and thought - hmmm I must remember this    http://blogs.msdn.com/b/psssql/archive/2011/09/01/sql-server-2008-2008-r2-on-newer-machines-with-more-than-8-cpus-presented-per-numa-node-may-need-trace-flag-8048.aspx   btw I think he means cores not cpus.

Now for an interesting one indeed: I've always been one to want to designate indexes/keys descending when tables have incremental counters as a key - or the key on a secondary index is a date and I know we usually pick the latest dates, as an example.  So my concern has always been that if I want the latest rows from a table I should have descending indexes; I seem to recollect somewhere in my distant past that this was actually important on a RDBMS ( not sql server ). So it's something i've been doing on and off for many years - the problem being that if asked to justify why i want to make an index descending could I prove that it made a difference?

Well earlier this year I attempted to prove that it did in fact make a difference ( sql 2008 and sql 2008 R2 ) but my attempts failed and other than it just making a select top xxx  always return the latest rows I wasn't able to show a difference.  Well today I found out how poor my attempts to show this were < grin >  and I bow down to Fabiano Amorim who has an article in simple-talk  which illustrates what I was trying to achieve.  http://www.simple-talk.com/sql/database-administration/statistics-on-ascending-columns/?utm_source=simpletalk&utm_medium=email-main&utm_content=ascending-20110905&utm_campaign=SQL

Things you overhear !!

"You can't get better than 90% can you?"

" we need to disaggregate down the data"

A New Record ( How many noughts ????????? )

I have to say I thought I’d seen it all but this last week brought an event of such magnitude I may be contacting the Guinness Book of Records. OK that’s not true, but I’d like to!

One of our production systems has to support a certain element of real time reporting, now OLTP applications don’t really go hand in hand with Reporting but to be honest not all applications clearly fall into one camp or another; for instance how do you place a financial system – well it certainly supports transactions, but you can’t put all the data in without looking at it – anyway this system difficult to classify.

As part of my monitoring I capture slow running queries as this helps in all sorts of ways, I record cpu, io and duration. Well today to my utmost delight one of our stored procs made an entry on my list with a stunning  1,292,836,673  page reads – impressive eh?

Now a page read is 8kb so I did a bit of rough maths, yup it isn’t very accurate I know, but if you work out the kbx the page reads this proc did the equivalent of reading the entire database 104 times over.

Now I think this is pretty remarkable for a piece of production code, this wasn’t a bug or a new release gone wrong either.

So is this a record or do you have procs which can beat this? Please let me know.

Sometimes I really wonder !!!

With all the best intentions in the world I did a review of some of our non production environments, what I realised was that we had not changed the default password on these environments for a number of years; essentially all user passwords are set to a default when cleansing the production environments. OK so far so good – I let everyone know and refreshed the environments.

Now let’s say that the original passwords were not exactly very secure – you’d maybe find them in a hackers dictionary.

What was somewhat amusing was the fact the testing team didn’t seem to realise we’d done this for some of the test environments for nearly two weeks .. hmmm!

Then the ultimate, to make life easier it seems some users decided to reset their passwords back to the ones that had been used for the past 4 years .. doh !

Then the final push which made me post this – walking out of the offices ( which are shared with other companies ) a caught the conversation between tow folks discussing access to their computer system “ It’s Admin  Admin, to get in “   aaarrgghhhh! 

I'd like to quote from a book I was given for fathers day  “Laugh on the loo”   -  “ Never underestimate the power of a bunch of stupid people!”

Posted by GrumpyOldDBA with no comments

Bare Metal Restore Part 2

I blogged previously about how Windows 2008 R2 has native "bare metal restore"   http://sqlblogcasts.com/blogs/grumpyolddba/archive/2011/05/13/windows-2008-r2-bare-metal-restore.aspx , see the Core Team's blog post here;  http://blogs.technet.com/b/askcore/archive/2011/05/12/bare-metal-restore.aspx

Well since then I’ve actually had the chance not only to put the process to the test but to see if I could go one step further.

I have a six identical IBM Servers, part of the development infrastructure, which I am rebuilding with windows 2008 R2, when one of these died I wondered if I could use the bare metal backup from a preciously built server to rebuild this one.
Creating images for building servers is nothing new, however I've never built a server this way before and I'm not aware of this feature ever being available out of the box in a windows o/s.


Just to recap on why you might want to to do this, when I built my first server I applied security patches and service pack 1 plus a number of other changes before I made my backup. This means the restore applied to another server avoids me having to go through all that again.


So the big question is can you use the windows 2008 R2 backup to build servers? The answer is yes it works perfectly and very quickly.
Did I have any problems? Yes as the backup wasn't created with this in mind so the computer name and ip addresses were duplicated which entailed a little bit of work.
When I build my next Server I will not name the server, leave it on dhcp and not add it to the domain; then I'll make my backup which I can use to build the remaining three servers as and when.

If you're wondering how easy it is to use this technique, I needed the o/s install dvd, a usb drive with the server storage drivers and another usb drive with the backup files created earlier, these were just under 11GB in my case.

If you're also wondering what this has to do with SQL Server, well you can use this backup to make an after install image for any SQL Server you've just built, it could also allow you to perform testing and then put it back where it was before. Obviously the more drives and applications the bigger your backup and I don't have a way to test if it works with SAN drives, but I see no reason why it shouldn't.

Despite some of the frustrations of "where is xxxx now !" I think praise is is due to microsoft for the advances in windows 2008 R2

More Posts « Previous page - Next page »