|
|
I got into a bit of a situation today. There were some performance issues in our performance testing environment and my changes (and a few others) were finally being tested. I was also told by various levels of management not to work on this project – concentrate my efforts on Project B. All I could think was my changes had to be using the correct indexes – how could I tell with out a lot of work – DMVs on index usage stats. I fired up google and found this from mssqltips.com. I changed the select a little and added the fairly accurate way to get row counts as a CTE. Here’s what I came up with:
;With RowCounts_CTE as
(SELECT
so.id,
[RowCount] = MAX(si.rows)
FROM
sysobjects so,
sysindexes si
WHERE
so.xtype = 'U'
AND
si.id = OBJECT_ID(so.name)
GROUP BY
so.id, so.name)
SELECT OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME],
I.[NAME] AS [INDEX NAME],
RC.[RowCount],
USER_SEEKS,
USER_SCANS,
USER_LOOKUPS,
USER_UPDATES
FROM SYS.DM_DB_INDEX_USAGE_STATS AS S
INNER JOIN SYS.INDEXES AS I
ON I.[OBJECT_ID] = S.[OBJECT_ID]
AND I.INDEX_ID = S.INDEX_ID
Join RowCounts_CTE RC on RC.id = S.object_id
WHERE OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1
Order by 1
I’m a runner as well as a DBA. I’ve got a marathon this weekend, so I started thinking about how SQL and running are similar.
- Small issues in your mechanics will show up later when performance is needed.
- You have your good days and your bad days.
- Sometimes you’ve got to grin and bear it and grind it out.
- Sometimes you’ve got to get up earlier than you want.
- You need goals and objectives to improve.
- Hard work is subjective to what you’re used to.
- You have to keep testing yourself to reach your highest potential.
- Sometimes you want friends to join you, sometimes you need some alone time.
- Someone is always better than you.
- Changing what you’re working on improves overall performance.
I’ve updated the script for Running Events to use the time data type for SQL2008.
Also here’s a little script I used to track my goal of running 200 miles in May. It gives a visual graph of actuals vs goal as well as percentage of month used.
Set nocount on
Declare @miles float
Declare @Goalmiles int
Declare @divisor int
--Change these based on your milage/goal
Set @miles = (8+10+11+16+12+10+13.5+22+11+11+11)
Set @Goalmiles = 200
Set @divisor = 2
Select Replicate('@', @Goalmiles/@divisor)
+ Char(13) + Char(10) +
Replicate('@', @miles/@divisor) + ' -- ' + convert(varchar(10), @miles/@Goalmiles) + '%'
+ Char(13) + Char(10) +
convert(varchar(10), @miles) + ' of ' + convert(varchar(10), @Goalmiles) as 'Progress'
--Find the percentage of the month gone
Select round(datepart(D, GETDATE())/convert
(float, datepart(D, DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)))), 4)
as '%ofMonth'
A few years ago I found this procedure on SQLServerCentral – I believe (but I can’t find it today) – it creates a random password with options for upper case, lower case, special characters and numbers. I liked this procedure as I’m kind of a security guy and I use it for all my random password needs. Note, this was written and tested for SQL Server version 7.0 and 2000 – a good thing lasts. Anyway, recently I’ve been doing some testing and in need of creating test data, so, not only did I create this script to populate a table, I’ve now been tweaking the password generator procedure.
Here’s the basic’s of it.
Select CHAR(ROUND(97 + (RAND() * (25)),0)) as 'Lowercase',
CHAR(ROUND(65 + (RAND() * (25)),0)) as 'Uppercase',
CHAR(ROUND(48 + (RAND() * (9)),0)) as 'Number',
CHAR(ROUND(33 + (RAND() * (13)),0)) as 'SpecialCharacter'
For Lower Case letters, here’s how it works:
- The RAND() function randomly picks a float from 0 to 1
- That random float is multiplied by 25, so it could be any number 0 through 25 – note 26 integers
- Add 97 to that, so the number now could be 97 through 122.
- The ROUND() function rounds that number to the nearest integer as it’s passing in a 0 for length — ROUND(<<Num>>, 0), so we’ve got integers 97 though 122.
- Finally, those integers are passed into the CHAR function, which converts an integer to a character.
The same idea’s apply to the upper case – except integers ranging from 65 to 90, etc.
Here’s a chart for the integer ASCII code to a characters
| Integers |
Characters |
| 97 – 122 |
Lower case |
| 65 – 90 |
Upper case |
| 48 – 57 |
Numbers 0 – 9 |
| 33 – 46 |
Characters !”#$%&’()*+,-. |
*Note the Char(10) is a line feed and Char(13) is a carriage return
Here’s a script to see all the integer ASCII code to a characters
Declare @TTable table (Number int, Character varchar(5))
Declare @TData int
Set @TData = 33
While @TData < 256
Begin
Insert @TTable Select @TData, CHAR(@TData)
Set @TData = @TData + 1
End
Select * from @TTable
Here’s a good example that uses this idea to create a view of a fixed length column – kind of a brute force method, but sometimes when your in a bind, it might just be crazy enough to work.
 
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 often need to fill up tables with random data to test data conversion scripts, foreign keys, indexes, etc. Here’s my “Go To” script for coming up with random data. I usually base it off this and change the name of the table to insert data into, but this has the things I need to remember, like an easy way to loop through code, NEWID(), RAND(), RND(), etc.
Declare @TestTable Table
(ID uniqueidentifier,
Num int,
InsertDate datetime)
Declare @icount int,
@TopNum int,
@Repeat int
Set @icount = 1
-------------------------
---Set the ranges here---
-------------------------
Set @TopNum = 10 --Highest number you want - note this will be 0 - 10
Set @Repeat = 1000 --number of times to repeat
-------------------------
-------------------------
While @icount < @Repeat
begin
insert @TestTable
Select NewID(),
ROUND((RAND() * (@TopNum)),0),
GETDATE()
Set @icount = @icount + 1
End
Select Num, count(Num) from @TestTable group by Num
order by Num
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.
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.
|
|
