|
|
 
The catch phrase around the office these days is “Big Trys and small Trans.” This came about as we were review stored procedure changes and I two suggestions. First, that the entire procedure has a try…catch around it and second that the transaction be as small as possible.
Big Try
We’ve got standards in place that procedures have a try…catch in them. One procedure had been copied, then modified with some logic to parse XML before the try. I said lets move the try to above the XML parsing in case there’s an error in the XML, we’ll catch that as well. So now we’ve got a bigger try.
Small Tran
The original code had the following steps:
- Start the transaction
- Look up some data
- Insert some data into a table
- Look up some other data
- Update another table
- Finally completed the transaction
In order to keep transactions small, I suggested the following:
- Look up all the data
- Start the transaction
- Insert the row into the table
- Update the row in the other table
- Finish the transaction
This will help avoid deadlocks and contention and keep things running smoothly. There’s the smaller tran.
If you stick with the “Big Trys and Small Trans” methodology, it should help the following:
- Help avoid contention
- Help avoid deadlocks
- Catch errors earlier
I’ve been working on some development work. This involves service broker messages and XML data. We wanted the XML data in basically the same format as the table, but with an additional root tag on it. To automate testing, I started digging into how to return XML via TSQL. The FOR XML AUTO, ELEMENTS option almost worked, except we needed a root element added to it. I toyed with the idea of converting to varchar, adding tags on the ends and converting back to XML – it would have worked, but that’s a hack. I went back to the basics and reviewed the Basic Syntax of the FOR XML Clause and found the root clause. Here’s an example of what I needed:
Create table ATest
(iID int identity(1,1),
Descripton varchar(50));
Insert ATest Values ('test1'),
('test2'),
('test3');
Select * from ATest FOR XML RAW ('ATEST'), ELEMENTS, ROOT('ATESTING');
This output is what I wanted:
<atesting>
<atest>
<iID>1</iID>
<descripton>test1</descripton>
</atest>
<atest>
<iID>2</iID>
<descripton>test2</descripton>
</atest>
<atest>
<iID>3</iID>
<descripton>test3</descripton>
</atest>
</atesting>
Seven base SQL Servers used for the web application:
- 3 – Clustered, SQL 2005 SP3 Enterprise edition
- 2 – Clustered, SQL 2005 SP3 Standard edition
- 2 – Stand Alone, SQL 2005 SP3 Standard edition
Three Log Shipping SQL servers:
- 1 – Clustered, SQL 2005 SP3 Enterprise edition
- 2 – Stand Alone, SQL 2005 SP3 Standard edition
One Utility Clustered Standard edition:
- 1 – Clustered, SQL 2005 SP3 Standard edition
I was the lead DBA on the most recent release of our newest version of our website. We moved all the databases to new servers and made a bunch of updates. Here were some of the challenges:
- Total data size – 2.2 TB of data
- SQL Servers crossing 4 domains and still communicating
- Changes to environment:
- Code changes for new functionality
- SQL2005 SP3 applied
- Litespeed upgraded from version 4.8 to 5.02
- New SQL Server in the environment and one DB moved to it
- Maintenance window reduction to 4 hours per week
- Security – Kerberos, double hop,
- Tightened,
- Changes in how hot fixes are deployed
- Tools – I3, Litespeed
- CPU Spikes post deploy – scheduled procedure wasn’t tuned properly
- LogShipping to get large DBs ready for deployment
- LogShipping Issues post deploy – restores missing files, manually restoring a log or two every few hours to keep things in sync.
Here are the current SQL 2005 Technologies that we are using in production:
- Clustering
- Partitioning
- Fragmentation based re-indexing
- Synonyms
- LogShipping with both native and LiteSpeed
I was On call this week and one of our databases files became over 90% full. This is the threshold we set to alert us to manually expand any file. We have autogrowth just in case, but we want to manually expand files after hours to avoid the performance impact on SQL server when expanding files.The question I had was do I wait for the maintence window, when services are shut off and no database activity is going on, or do I expand the file after business hours when there is minimun activity. One of the things we’ve been working for is a maintenance window reduction, so for future reference, I needed to know what the performance impact was – not to mention the fact that the maintenance window is at 2 AM and with a baby, I wanted all the sleep I can get.Here’s what I’ve found – any data changes that cause a page split or an additional page will be suspended, pretty much everything else will work – a good explanation here -
Any operation that depends on a new extent allocation will suspend while the data file is being expanded. If you are inserting on a non-full page, it will work. In-place updates will work. New rows that trigger an extent allocation will suspend.
So here’s what I did – I added 5Gb to the 700 in a non-peak time and it took 1 minute and 30 seconds. During this time, I monitored for blocking, there was none. My goal was to grow it 15 Gb total, so I repeated this process two more times.
Backups and Backup StrategiesMicrosoft’s SQL Server Backup Overview
http://msdn.microsoft.com/en-us/library/ms175477.aspx
What we use (all definitions taken from link above):
1. Full backups
A full backup contains all the data in a specific database or set of filegroups or files, and also enough log to allow for recovering that data.
2. Differential backups
A differential backup is based on the latest full backup of the data. This is known as the base of the differential, or the differential base. A differential base is a full backup of read/write data. A differential backup contains only the data that has changed since the differential base. Typically, differential backups that are taken fairly soon after the base backup are smaller and faster to create than the base of a full backup.
3. Transaction Logs
Under the full recovery model or bulk-logged recovery model, regular transaction log backups (or log backups) are required. Each log backup covers the part of the transaction log that was active when the backup was created, and it includes all log records that were not backed up in a previous log backup.
Notes:
Our naming conventions are based on: 3 characters Location Code 2 characters domain 2 characters site code 2 – DB for Database 2 Numbers for server # 3 – SQL for clustered server Differential backups overview….not dependant on other differential backups
| Sunday |
Monday |
Tuesday |
Wednesday |
Thursday |
Friday |
Saturday |
| Full Backup |
Differential Backup |
Differential Backup |
Differential Backup |
Differential Backup |
Differential Backup |
Differential Backup |
| Everything |
X |
X |
X |
X |
X |
X |
| |
|
Y |
Y |
Y |
Y |
Y |
| |
|
|
Z |
Z |
Z |
Z |
| |
|
|
|
W |
W |
W |
| |
|
|
|
|
Q |
Q |
| |
|
|
|
|
|
T |
To restore to w, all you need is Sundays full backup and Thursday’s differential backup
Logfile backups overview….dependant on other log files backups
| 2:00 AM |
5:00 AM |
8:00 AM |
11:00 AM |
2:00 PM |
5:00 PM |
8:00 PM |
To restore to w, you need is 2:00 AM full backup, 5:00 AM log backup, 8:00 AM log backup, 11:00 AM log backup and 2:00 PM log backup.
General DBA Responsibilities
- Availability/Recoverability
- Security
- Performance
- Develop/Deploy
RecoverabilityRecoverability means that, if a data entry error, program bug or hardware failure occurs, the DBA can bring the database backward in time to its state at an instant of logical consistency before the damage was done. Recoverability activities include making database backups and storing them in ways that minimize the risk that they will be damaged or lost, such as placing multiple copies on removable media and storing them outside the affected area of an anticipated disaster.SecurityPrincipals are entities that can request SQL Server resources. Like other components of the SQL Server authorization model, principals can be arranged in a hierarchy.Windows-level principals
- Windows Domain Login
- Windows Local Login
SQL Server-level principal
Database-level principals
- Database User
- Database Role
- Application Role
SQL 2005 Versions
- Express – Free and lite, sometimes distributed with applications, run on desktop – RAM, DB size, etc limitations
- Workgroup – Less features than standards not used too often
- Standard – four CPUs max, no partitioning – we use this on all but servers with partitioned tables
- Enterprise – All the features
Full Explanation here….http://www.microsoft.com/Sqlserver/2005/en/us/compare-features.aspxIntroduction to General SQL Architecture SQL has 2 main servicesSQL Server – runs the serverSQL Agent – runs schedulerSystem Databases – Master, Model, MSDB, TempDBUser Databases – 2 filesData File – .mdf (default extension)Log File – .ldf (default extension)Overview of SQL Server 2005 for the Database AdministratorGood site that explains lots of the features and services SQL2005 can provide including…
- SQL Service Broker
- Reporting Services
- Notification Services
- High Availability and Scalability options
- Plus more…
SET CONCAT_NULL_YIELDS_NULL OFF
SELECT weighted_cost = avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),
s.*,
'CREATE INDEX IX_' +
REPLACE(REPLACE(COALESCE(equality_columns, inequality_columns), '[', ''), ']', '') +
' ON ' + d.statement + '(' + COALESCE(equality_columns, inequality_columns) +
CASE WHEN equality_columns IS NOT NULL
THEN
CASE WHEN inequality_columns IS NOT NULL
THEN ', ' + inequality_columns
END
END + ')' +
CASE WHEN included_columns IS NOT NULL
THEN ' INCLUDE (' + included_columns + ')'
END
FROM sys.dm_db_missing_index_details d
INNER JOIN sys.dm_db_missing_index_groups g
ON d.index_handle = g.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats s
ON g.index_group_handle = s.group_handle
ORDER BY avg_total_user_cost * avg_user_impact * (user_seeks + user_scans)DESC;
I would suggest once a quarter, set up a performance monitor log to collect the following counters for a couple of 24 hour periods. Look at the results in both a graph and report forms to determine the peak usage times and to see if there are any bottlenecks on your SQL server.
Here are the counters to monitor:
Memory: Pages/sec
Average between 0 and 20
spikes ok
Memory: Availible Bytes
Average > 5 MB
SQL Server: Buffer Manager: Buffer Cache Hit Ratio
> 90 closer to 99
Physical Disk: Disk Reads/sec
Watch for Growth over time
value depends on hardward
Physical Disk: Disk Writes/sec
Watch for Growth over time
value depends on hardward
Physical Disk: % Disk time
< 55%
if > 55% for more than 10 minutes may be an issue
Physical Disk: Avg. Disk Queue Length
< 2*individual drive in an array
i.e. < 10 is ok for 5 disc raid)
if > for more than 10 minutes may be an issue
Physical Disk: % Free Space
> 15%
Logical Disk: % Free Space
> 15%
Processor: % Processor Time
< 80%
Spikes ok
if > 80% for more than 10 minutes may be an issue
System: Processor Queue Length
< 2 per CPU
if > 2 per CPU for more than 10 minutes may be an issue
Network Interface: Bytes Received/sec
A sudden unexpected increase could be an external attack
Network Interface: Bytes Sent/sec
A sudden unexpected increase could be large volume of information being accessed
Network Interface: Output Queue Length
< 2
If NDIS hardware – this should always be 0
SQL Server: General: User Connections
< 255
if > 255 for more than 10 minutes may be an issue
These are the basics, keep a report of the findings and compare them to past results each time you run them.
|
|
