|
|
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.
Here’s what I’ve been working on this week
- We’ve added a new SQL server to our staging environment that has a database for archived data.
- Spent a bit of time troubleshooting the deployment process of this.
- Added four part names to synomyns that need to use a linked server to get to the archived data
- Identified and disabled triggers that aren’t needed in the archive process to increase performance
- Coordinated building and QAed our Prod-Staging servers for our US Site
- Five clusters and two stand alone servers built, configured and QAed by other members of my team
- Production databases restored on them
- Our code deployed to our new release
- Coordinated building and QAed our Prod-Staging servers for our non-US Site
- Five stand alone servers built, configured and QAed by other members of my team
- Production databases restored on them
- Our code deployed (different rev than US site) to our new release
- Applied SQL2005 SP3 to these
- OnCall for production
- Deadlocks on our Transaction Log backups for log shipping with LiteSpeed
- Task completes successfully, but gives the deadlock error.
- Altered Scheduling – reduced the problem, but didn’t fix it.
- One of our database’s files (which is 700GB) became over 90% full, so I needed to expand it.
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.
|
|
