How do I calculate database growth 12 months from now?

October 6, 2014 Leave a comment

I often get asked a question similar to that; I need to figure out how to calculate what my database size will be 12 months down the road, or 24 months, or 36 months?  But how do I do that?

There is a simple formula we can use to calculate this; however in order to do it we need to know few key values.

  • What is current size? (CurrentSize(mb))
  • What is the expected monthly growth in percent? (ProjectedGrowthPerMonth(%))
  • How many months we want to know growth for? (NumberOfMonthsToProject)

GrowthFormula

So lets take this formula and try to calculate the growth for a database.  Lets say we have a database that is 10240MB and it is projected to grow by 10% per month.  What would be its size in 12 months from now?

NewSize (MB) = 10240MB * [(1 + 10%) ^ 12] = 10240MB * [1.10 ^ 12] = 10240MB * 3.14 = 32153MB approx.

We can verify our final value by doing manual calculation like so …

Month Size @ Start Growth Final Size
1      10,240.00    1,024.00    11,264.00
2      11,264.00    1,126.40    12,390.40
3      12,390.40    1,239.04    13,629.44
4      13,629.44    1,362.94    14,992.38
5      14,992.38    1,499.24    16,491.62
6      16,491.62    1,649.16    18,140.78
7      18,140.78    1,814.08    19,954.86
8      19,954.86    1,995.49    21,950.35
9      21,950.35    2,195.03    24,145.38
10      24,145.38    2,414.54    26,559.92
11      26,559.92    2,655.99    29,215.92
12      29,215.92    2,921.59    32,137.51

We can see the final size is approx same as one calculated above. Most likely difference occured because I rounded off the “3.14”; where as table above is generated via Microsoft Excel handling the rounding for me.

Help! How do I find disk I/O problems?

October 3, 2014 Leave a comment

This is a big topic, so the advice below is not meant to be comprehensive; but more of generaly troubleshooting walkthrought that you can use to try to identify the problem.

Disk performance generally is measured in the disk latency we are experience for reads and writes.  For reads going to disk we would like to have disk latency between 8 – 15 ms (measured by Avg. Disk Sec/Read) and for writes with cache backup we would like disk latency to bet between 4 – 8 ms (measured by Avg. Disk Sec/Write).   In addition to these we need to understand how my IOPS (Input/Outputs Per Second) are being generated on the server.  These counters can be measured by (Disk Reads/Sec, Disk Writes/Sec, and Disk Transfers/sec).  We need to understand the IOPS because generally there is upper limit on amount of IOPS a disk can handle.  For example a 15000 RMP disk can handle approx. 150 – 225 IOPS/sec; or if we are working with SAN then undestanding the ture volume can be benfical.  It might be difficult to assess how many IOPS your SAN can support; however you can have an intelligent discussion if you know what are the IOPS you are generating.

For SAN communication through Fiber Channel, we have to also consider the HBA Queue Deapth Setting.  By default HBA cards Queue Depth Length now days is set to 32.  Which is usually sufficient, however we can use SQLIO (stress testing tool) and SQLIOSIM (testing San/server with similar I/O patterns as SQL will generate), to test other settings; in some cases higher HBA queue depth setting can lead to better performance.  Recommend working with SAN and HBA vendor to figure out best setting for you.

After this we have the throughput being generated, we can look how many bytes are being transferred per second (Avg. Disk Bytes/Transfer).  This can help us assess the total data being pushed through HBA (Fiber Channel) or Network (iSCSI).  A 4Gbs HBA can support 476MB/s throughput at MAX; a 10Gbs can support 1192MB/s throughput at MAX.  However both these methods have limitations defined by the fiber and switches they must travel through and the speed negotiate.

We also like to look at the Avg. Disk Queue Length for both Reads and Writes (Avg. Disk Read Queue Length, Avg. Disk Write Queue Length, and Avg. Disk Queue Length).  It is hard define a range for these values because this calculation used to be done by knowing the number of spindles assigned to a LUN.  However in most modern SANs that is difficult to assess.  That said, it is still important this number is large like 30 or 40+; that still represents an issue. If that happens you should see multiple I/O stalls in SQL Server and disk latency should be poor.

These counters together with underline hardware configuration understand can be used to understand the bottlenecks on the disk subsystem.  For example, lets say we have a blade center enclouser with 16 servers.  Which has 4 HBA cards, each being 4Gbs (total throughput of 1904MB/s) being shared amount 16 servers in total.  However each server in blade center can access at most two HBA at any given time, therefore each server effective throughput is limited to 952MB/s.  So therefore 952MB/s is being shared by at least 8 servers within the enclosure; without knowing the other server workload and assuming equal distribution; we are left with final bandwidth limitation of 119MB/s per blade centre server.  Therefore having understanding of underlying hardware can help use runderstand the numbers we collected; are they an issue or they with in acceptable parameters.

Other indication of the I/O subsystem bottle neck is we start seeing stalled I/O messages with in SQL Server error logs.  In addition to this you can also see stall i/o requests with in SQL Server DMVs (sys.dm_io_pending_io_requests) and the waits being generated on disk by looking at latch waits in (sys.dm_os_wait_stats or sys.dm_os_latch_stats).

Enterprise Policy Management V4.0 Released

October 1, 2014 Leave a comment

EPM is great tool that builds on top of the SQL Server Policy Mangement and Central Management Server to provide you overview of your enviornment.

It is fairly easy to implment and EPM team has made many updates to the indexes, statistics, to improve its performance.

Please check it out @ http://epmframework.codeplex.com/

SQL Server PFE Position in Canada

September 30, 2014 Leave a comment

ROLE DESCRIPTION

The Premier Field Engineer provides advanced on-site and remote technical troubleshooting support and problem resolution for Microsoft Premier Customers. Candidate must be able to resolve complex issues escalated to the highest levels of management while ensuring satisfaction with Microsoft software by setting customer expectations, devising and implementing action plans and professionally communicating to all parties involved.

https://careers.microsoft.com/jobdetails.aspx?ss=&pg=0&so=&rw=6&jid=146017&jlang=en&pp=ss

Check it out guys and gals; if your intersted, feel free to ping me here or linked in :).

Categories: Announcement

SQL Server 2008 SP4 and SQL Server 2008 R2 SP3 Released!

September 29, 2014 2 comments

SQL Server 2008 SP4 (link) and SQL Server 2008 R2 SP3 (link) have been released.  Please note these are the final service packs for these products is now in Extended Support cycle of Product Support Life Cycles.

Update Sept. 30, 2014: Sorry for mistake, for SQL Server 2008 it would be SP4.

Updating Central Management Server (CMS) Register Server List Automatically

August 18, 2014 Leave a comment

Microsoft released Central Management Server (CMS) with SQL Server 2008; it allowed us to have great functionality for multi-server management.  However keeping that list up-to-date can be time consuming and tedious.  For multiple reasons, primary reason because we are only DBAs, so we don’t get told anything.  We are expected to just know it right!?!

Okay, I will not get into rant about that topic.  Anyhow SQL Server instances get installed in organization (without DBA knowledge) or get uninstalled (again without DBA knowledge).  But you have the servers in your CMS registered server list; or would like to get those new servers in that list.  So how can we go about it?

There is no easy way to discover all instances in your domain; one of the few methods are:

  • SCOM
  • SCCM
  • MAP Toolkit

Probably few other, which I don’t know about.  But point is there is nothing Native to SQL Server.  So when one of my clients asked “How can we update CMS automatically?”  Only thing I could think of is SCOM.  So here it is, a PowerShell solution, that updates CMS from SCOM.

If your interested in trying it out please download a copy from Copeplex, here.  It is easy to setup, the documentation on Codeplex site includes instructions.

If you use it, please leave feedback :).

Auditing SSRS Report Execution

August 11, 2014 Leave a comment

We can use the SSRS Report Server Execution Logs (link) to review who is executing reports on the server.  These logs are saved directly in the SSRS database Report Server.  There are three views available to us to show the information on who executed the report, how long the execution took, and parameters were used.

However by default the data in the views is only kept for 60 days.   We can change that however via SSMS.

  1. Open SSMS.
  2. Connect to Reporting Instance.
  3. Right lick on Reporting Server Go To Properties.
  4. Go to Logging.
  5. Checking the Remove Log Entries Setting as per in screenshot below.
  6. Click Ok.

SSRS_SettingsI would not recommend making this number large; as it will bloat your database.  Also please don’t give access to people to run queries against this view.  Instead develop a view with filtered set and develop a report that is published against the new view.  Because the execution log contains parameter information, it might also hold sensitive information.

SQL Server Configuration Manager, WMI Error 0x80041010

August 4, 2014 Leave a comment

Just got this at my client, its normal message.  See it way to often, generally I run into this when the multiple version of SQL Servers are installed.  So decided to do a bit of research on this, namely why does it happen?  Some reasons include …

  • A SQL Server instnace on the server was uninstalled inturn it uninstalled shared compontents relarting to WMI.
  • During SQL Server installation WMI doesn’t get registered successfully.

7522_SQL_Server_Configuration_Manager_Invalid_Class_0x80041010

Its easy to fix, as per KB956013, execute following command and restart WMI Services.

mofcomp “%programfiles(x86)%\Microsoft SQL Server\number\Shared\sqlmgmproviderxpsp2up.mof”

If executed successfully you should see a successful message.  If you get an error, make sure you are running the command prompt as Administrator.

Service Principal Names for SQL Server

June 16, 2014 1 comment

Service Principal Names (SPN) get used for Kerberos authentication.  SQL Server require that all instances have SPNs configured, if not Kerberos authentication fails.  By default starting with Windows 2008 all communication between clients and SQL Server is first attempted on Kerberos.  If it fails, it falls back to NTLM.  Often if you have Kerberos issues you might also have SSPI error messages in your SQL Server errorlog.

So question can be asked, if it falls back to NTLM and I can get my work done.  Why do I care?  Simple answer is delegation.  With incorrect configured Kerberos, delegation cannot be completed and logins fail.  For example on delegation, read my post on Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’ with SQL Server Reporting Services.

So what SPNs we need? Simple answer two SPN per SQL Server instance.

  • MSSQLSvc/SQLServerName[:Port] Domain\SQLServerServiceAccount
  • MSSQLSvc/SQLServerName.FQDN[:Port] Domain\SQLServerServiceAccount

Depending on your configuration, SPNs can look different.  We can manage SPNs two ways; 1) using SETSPN utility 2) using Activity Tool call EDITADIS.  Most people rely on SETSPN, as other tool is for Active Directory administrators.  And they (AD Admins) don’t like it when we mess around with their stuff; like we don’t like it when they mess around with our stuff ;-).  So lets talk about various forms of SPNs you can create.

Example #1: Default Instance, Default Port (1433), Using Domain Service Account

  • MSSQLSvc/SQLServerName Domain\SQLServerServiceAccount
  • MSSQLSvc/SQLServerName.FQDN Domain\SQLServerServiceAccount

Example #2: Default Instance, Non-Default Port, Using Domain Service Account
Example #3: Named Instance, Static Non-Default Port, Using Domain Service Account

  • MSSQLSvc/SQLServerName:Port Domain\SQLServerServiceAccount
  • MSSQLSvc/SQLServerName.FQDN:Port Domain\SQLServerServiceAccount

Example #4: Default Instance, Default Port (1433), Using Computer Account *

  • MSSQLSvc/SQLServerName Domain\ServerName$
  • MSSQLSvc/SQLServerName.FQDN Domain\ServerName$

Example #5: Default Instance, Non-Default Port, Using Computer Account *
Example #6: Named Instance, Static Non-Default Port, Using Computer Account *

  • MSSQLSvc/SQLServerName:Port Domain\ServerName$
  • MSSQLSvc/SQLServerName.FQDN:Port Domain\ServerName$

Example #7: Named Instance, Dynamic Non-Default Port, Using Domain Service Account **

  • MSSQLSvc/SQLServerName:InstanceName Domain\SQLServerServiceAccount
  • MSSQLSvc/SQLServerName.FQDN:InstanceName Domain\SQLServerServiceAccount

Example #8: Named Instance, Dynamic Non-Default Port, Using Computer Account *

  • MSSQLSvc/SQLServerName:InstanceName Domain\ServerName$
  • MSSQLSvc/SQLServerName.FQDN:InstanceName Domain\ServerName$

* An instance is consider using computer account when it is running under built in accounts, such as Network Services.
** Generally we do not recommend dynamic port as managing SPN manually can be difficult.  Also for us to use InstanceName, the SQL Server Browser service must be running.

So after reading this you are going that’s TOO MUCH TO REMEMBER!

I agree, that is why SQL Server can create and destroy this SPNs by itself without user interaction. However in order to do this, we must grant SQL Server Service Account or Computer Account permissions to Read/Write SPN Property on itself (Reference).

Now there is one scenario where we do not want to grant this permission and mange the SPN manually.  Please reference KB2443457. Copied the issue from KB for completeness.

  1. The Sqlcluster instance is active on Node A and registered the SQL SPN in domain controller A during start up..
  2. The Sqlcluster instance fails over to Node B when Node A is shutdown normally.
  3. The Sqlcluster instance deregistered its SPN from domain controller A during the shutdown process on Node A.
  4. The SPN is removed from domain controller A but the change has not yet been replicated to domain controller B.
  5. When starting up on Node B, the Sqlcluster instance tries to register the SQL SPN with domain controller B. Since, the SPN still exists Node B does not register the SPN.
  6. After some time, domain controller A replicates the deletion of the SPN (from step 3) to domain controller B as part of Active Directory replication. The end result is that no valid SPN exists for the SQL instance in the domain and hence you see connection issues to the Sqlcluster instance.

Blog post cross posted on …

  • SQLCAN (WordPress), Link.
  • SQLCAN (MSDN), Link.

Online Index Rebuild can Cause Fragmentation

June 9, 2014 Leave a comment

As Microsoft SQL Server PFE, I am always playing with SQL Server.  I generally spend most of my time on the engine side of world; so I am somewhat of a newbie when it comes to BI.  So I understand when I see something in BI world and am a bit lost.  However when I ran into this, rebuilding index online caused fragmentation.  I was some what baffled. You can try it if you wish, I tested it on SQL 2008, SQL 2008 R2, and SQL 2012.

-- Create table for testing.
CREATE TABLE Test (Col1 INT IDENTITY(1,1), Col2 CHAR(50), Col3 CHAR(50), Col4 Char(50)
CONSTRAINT pkTest PRIMARY KEY
(
        [Col1]
) WITH (ALLOW_PAGE_LOCKS  = OFF)
)

-- Insert test data.
INSERT INTO dbo.Test (Col2, Col3, Col4)
SELECT TOP 1000000 REPLICATE('A',50),REPLICATE('A',50),REPLICATE('A',50)
FROM sys.all_objects O1
CROSS JOIN sys.all_objects O2

-- Check fragmentation.
SELECT * FROM sys.dm_db_index_physical_stats(db_id(),null,null,null,null)

-- Rebuild Index online using MAXDOP
ALTER INDEX pkTest ON Test
REBUILD
WITH (Online=ON, MAXDOP=8)

-- Check fragmentation.
SELECT * FROM sys.dm_db_index_physical_stats(db_id(),null,null,null,null)

Some of you might noticed an interesting property.  ALLOW_PAGE_LOCK.

So turns out this is by design.  If ALLOW_PAGE_LOCK is turned off, we are building a index using parallelism, and building it online; it can cause fragmentation (Ref 1 & Ref 2).

Well lesson learned for future and no, it will not be last one.

Follow

Get every new post delivered to your Inbox.

Join 237 other followers

%d bloggers like this: