Adventures with Powershell, turning it into BAU

Having tamed the security beast for the time being I have now been able to get my version of server monitoring tool in place and running reliably for a few days.

The biggest issue I have encountered though is that our service provider has supplied us with such an array of down servers that I haven't had a chance to roll out the process to any more machines.

I now have a process that breaks into multiple parts (with some documentation for whoever comes after me) broken into several "creation" files to keep functionality clear and simple having chosen a target SQL server (tested on 2008R2 as the host and 2005, 2008R2 targets (Standard, Enterprise & Express editions) :

1. Tables Script - creates all the tables, indexes and a view to reduce my typing when looking at results. I have included in the process a "ProcessLog" table, I have used an identical design as I have in place on a live application on another box and one day I might well merge the two together (which has prompted me to create a to do list).
2. Check State process creation - creates the requisite stored procedure on the host machine.
3. Report State process - creates separate functionality for reporting the state retrieved from the check state process, I separated the two because originally this was configured to email results (HTML formatted) but in testing this got annoying when I had slow / non-functioning email so I added a parameter to present output locally; it was tidier to separate checking a state from reporting on states so allowing me to run the check process once and check the results repeatedly without waiting - handy for multiple users.
4. Manage history - each time the state checking occurs it moves all the current data to a history table - that way the live data is uncluttered whilst we keep a record. The maintenance process takes two parameters, firstly how long to keep any data and secondly how long to keep "issues" - that way I can, for instance, say I'm only interested in a complete log of service state information for 7 days but I'd like to keep servers which are not reported as active for 3 months. Handy for pointing at in discussions with service providers.
5. Scheduling process. Via my 1-click install process I generate two "DBA Maintenance Tasks" - one called "Daily" and one "Weekly" (I suspect you know how often they run). But this checking should ultimately run more frequently (that will depend on your enviroment & execution time, criticality / SLAs and how on the ball the server boys are), I am thinking ultimately once a hour for normal use (and I have added another to do item, logging checking periods).
6. A deployment record. Currently this is a simple Excel sheet that shows the date of the last code update (manually entered) and a list of servers with deployment dates (all manual) with  conditional formatting to highlight out of date deployments (another to do item is to make this a tidier SQL process).

I also have a 1-click install process (I'll write about this one day) that deploys all of my maintenance routines to a specified machine. What I've done to that is add "monitoring server" to the list of variables configured (yes you'll need something reliable) and updated the weekly maintenance routine to add a step for the history management action if the 1-click install is running on the monitoring server (the 1-click install is also coded for redeployment of updated code). I also automatically add a new server to the monitoring list assuming a default checking frequency.

Next I'll go through the code that does the work.

Adventures with Powershell, knocking down the security barrier....

I have now had my routine for checking service status running for a few days but am still discovering new circumstances which need consideration. I have accounted for the obvious "server uncontactable" circumstance which is part of the point of the process but I have since altered my code further to ensure there is a catch all for circumstances I have yet to identify.

One additional circumstance has raised its ugly head above the parapet. On testing one server was not  responding via the script. Running the Powershell script directly against the server resulted in a perfect run from several different machines. But running the same script via xp_cmdshell caused a failure.

I finally had to raise the query on MSDN and promptly got the answer I needed. What I was unaware of is that inspite of running interactively via SQL Server Management Studio, xp_cmdshell runs with the rights of the SQL Server Agent account, which in this instance had not got rights to the destination machine.

Simple fix, I would imagine for production use a dedicated proxy account should be put in place on all monitored boxes to ensure limited rights but we're in action again.

Adventures with Powershell, first steps...

I keep hearing about this mysterious product that is now bundled with SQL Server in various guises called Powershell.

I've made the occasional foray in it's direction to find a DOS prompt with all the functionality that should be in a DOS prompt, in fact, something surprisingly like a Unix command line but in DOS, off course I'll probably find a horse's head next to me in the morning for suggesting such heresy.

Anyway, to date I found no practical purpose for me or the environment that I manage because everything it seems to offer I can do already via SQL Agent without having to learn arcane Powershell scripting and magic words.

But no, I have one nagging issue.

Prior to some recent data centre activity we DBA types had configured a job that collected, on a daily basis, stats from each SQL server and stored them at a single point. This was entirely hand carved using t-sql, ssis & the SQL agent & worked beautifully on SQL Server 2000, 2005 & 2008R2. The only flaw was that the first method coded, and due to time constraints never altered, the code worked in an entirely linear and fault intolerant fashion - so if server A was uncontactable the SSIS step would fail and the whole process failed for the day. This would have been fine if it wasn't for service companies who seem to make it their mission to make networks about as unreliable and poorly performing as possible to keep themselves in work while not having to tax themselves too hard to be forced into remedying the situation.

So, four years of living with an unreliable but generally working situation became the accepted norm due to massive volumes of actual work - why bother monitoring or checking when there are dozens of calls a day telling us how bad things are.

This cannot continue.

Enter the next service provider who promises everything will be fabulous, they look after these babies in their sleep. Contract signed and responsibility for managing services on Windows servers duly ensconced with said service provider who has to tools to manage the situation.

Said tools, yours truly & colleagues, don't cost the service provider a penny but do provide a regular parade of helpdesk calls along the lines of "service x isn't working, the users are jumping on our heads, why didn't you notice as they've started wearing studs". This is of course good for the service desk because it keeps their volume of calls up whilst they can file under first time fix because the same said tools have had to resort to managing the services themselves.

This toolset is actually pretty good because you can find extensions of the toolset sitting in other teams who merrily report directly to the biggest tools that server x is down again, naturally they won't report it to the helpdesk because they don't actually help and as a change to the environment (bringing server back up) is a change request and investigating why it went down is complex enough to be handed to third line but because it mentions "database" in the text somewhere they shunt it into the DBA queue which puts us all in limbo because the DBA queue is not actively monitored since the same service provider decided that staff of the client in non application support roles can't use the ticketing system.

I'm feeling better now.

So the fundamental problem is that we need to know when servers or services are down before anyone from the user community has a chance to nip home for their stilettos. We cannot rely on the service company for anything. What we need is a means of checking server status and service status whilst minimising the need to connect to SQL boxes but still keeping control of the entire process within the SQL world so that we can stop anyone else scuppering us further.

Enter Powershell (I got there in the end).

There is an interesting article I came across recently that introduces something very close to what I need written by Jugal Shah on MSSQLTIPS.

The principle works nicely - Powershell appears rather wordy so performance isn't great (30 seconds to check 4 servers) so it's not going to running every few seconds but anything around an hour will do fine for our purposes and I suspect there must be some ways to reduce the volumes of text and hence run times.

What it doesn't do is put any control and coordination around the principle that we would expect in our environment, so next time I'll post our implementation which works in our environment.

Progress Updates

A colleague not too long ago introduced me to sys.dm_exec_requests which has a percent_complete column and has significantly changed our view of longer processes and lends a degree of confidence where before we would be sitting there wondering if it was time to reboot.

 I have, however, encountered a notable problem, on a reasonable sized database restore (this will depend on infrastructure but I've seen this above 100Gb) progress sits at 0% for what seems like at eternity.

In fact the timing before progress starts getting logged has seemed to be to be about how long it would take to create the file and space required and do almost all of the restore process.

I have now discovered more detail that might be useful next time I encounter the "problem" because it appears that recovery is split into 5 phases :

1. Copy of data from the backup to database pages.
2. Discovery 
3. Analysis 
4. Redo 
5. Undo 

where on Enterprise edition (2005+) the database is available to users from the end of phase 4 (using Fast Recovery) and on old versions & other editions the database is available after the undo phase (more on that here : fast-recovery-with-sql-server .

I think I will find that SQL is reporting 0% while the data is being copied back to the data files and given that this will be happening as a transaction it remains 0% until completed which explaims the behaviour being seen.

It seems that the view sys.dm_tran_database_transactions will give further detail during  the undo phase and where the database_transaction_log_bytes_reserved column will show decreasing volumes for the given database.

Further, where there are several transactions to undo the database_transaction_next_undo_lsn column will provide additional detail.

Memory issues

I've a SQL script that produces cumulative waits (sys.dm_os_wait_stats), key items for review (same source & gives % of all waits to each type but ignore "benign" wait types) and then reproduces the same information but as a delta so current waits can be seen and finally looks at current activities with waits so it is possible to see what is hurting (via sys.dm_os_waiting_tasks and sys.dm_exec_sessions / sys.dm_exec_requests).

This gives me a broad summary of what's going on an is a good indicator of where to look when there are problems, what's more it generates a good viewpoint without taking an age to run which is always good news as the only boxes this needs to be executed against are those with problems.

What I also have is an accumulated list of wait types and meanings, I'm not sure where I picked up the initial list but as time has gone on I've added to and clarified meanings. One area I have been looking at lately is memory grants because I had a server which continually has outstanding memory grants - within the last 12 months we've gone from 16Gb to 40Gb and I think the machine would still like more. The core database suffers a range of problems include the wrong fields / no fields being indexed, uniqueidentifiers, non unique primary keys and a failure to agree an archive policy prior to implementation but that's a little out of scope here. What I do know is that the machine is now largely stable but could do with more memory (EDIT:10 days on and performance problems have persisted and more RAM would definitely be good).

During the course of investigating I have collected further information about the RESOURCE_SEMAPHORE wait type that appears to link wait types to memory shortages which ties in with the grants outstanding issue.

In this article MSSQL Tips : Resource_Semource waits the requested_memory_kb column from sys.dm_exec_query_memory_grants is highlighted showing how much memory has been requested and then talks about looking at sys.dm_exec_query_memory_grants and sys.dm_exec_sql_text(sql_handle) to look at the largest memory requests.

Having been really rather instructive the article then fizzles out and concludes the answer is to look at indexes or lack thereof.

That is good starting point and all to often basic indexes are the fix but indexes can be a minefield, vendors are often very sensitive to making changes  and if they discover changes have been made without agreement then blame all future problems on that one things - I had a vendor once offer to accept the idea of creating two indexes (which I had tested and transformed their system performance) but having received the creation statements from me wanted to charge £800 to allow us to run the statements (I would have been the one running them in the production instance). Further, changing indexes on a mature system can often lead to unexpected effects that are difficult to predict without testing the application in greater depth.

Looking further at the memory requested I would suggest that there is a case to consider other factors too, yes they will be equally contentious with vendors but adding indexes might be a lazy solution :

1. Are joins of the correct type (can that outer be replaced with an inner) and are all join criteria specified
2. Are functions being executed against fields in the where clause that might impact index usage (perhaps in the future, always worth checking as comparisons against variables might be alterable to modify the variable).
3. If the query loading tables and fields that add no value.
4. Are there varchar fields involved - the query optimiser will estimate that varchars take half their defined length for memory requirements usually - have a looked at "Estimated Row size" in the query plan - if your varchar fields are frequently using a lot less than or more than half their defined size the estimates of row size might be a long way out especially for longer fields. You can test this for yourself, turn on the actual query plan and try :
   
   select cast('hello' as varchar(30)) from
   
   Right click on the "compute scalar" item and have a look at the estimate row size (I see 29Bytes), now change the varchar(30) to text or varchar(max), nvarchar(30) or anything else and see what sizes you get. For varchar(max) I get an estimate of 4035B - in the table I picked I have 129 rows so the difference is (129 * 4035) - (129 * 29) - thats 504Kb difference on just 129 rows, it'll soon add up.
5. How many rows does the query plan report will be retrieved - I've just been looking at a query that said it was retrieving 233,131 rows which didn't match the 136 rows I could see in the result set. Looking further at the query and the context from Profiler I can see that the query is generating a parameter list for an SSRS report - still too many rows though but the "distinct" reveals why I see so few records. More examination clarifies that the number of records reported is because the developer wants to get "types" of something that are used and is achieving that by an inner join between the types table (225 rows) and the "items" table that has 15m rows - replacing the inner join with an exists reduces the impact somewhat - runtime reduced from 12 seconds to less than 1.

I'm not saying this is the be all and end all of query optimization, it's a massive subject and I am grateful the optimizer does a brilliant job most of the time, I am though trying to highlight other potential issues.

3NF reminder

Normalisation can be a mine field for some but I find that when I try to normalize data I don't have 3 key steps to get to 3NF, I just end up with a single operation that weeds all three things in one go.

I am conscious that 3 steps becomes more relevant for unfamiliar situations and for newbies but the descriptions I learned :

1. Remove repeating groups
2. Full functional dependency
3. Transitive dependency

are pretty useless to succinctly describe the purpose - I've recently tried to narrate to a newbie what these mean and ended up with a less than brilliant explanation which made little sense to me let alone someone starting out.

However, I have recently I came across this meaningful summary :

1. No repeating elements or groups of elements
2. No partial dependencies on a concatenated key
3. No dependencies on non-key attributes

which explain everything in a rememberable form http://phlonx.com/resources/nf3/

Configure Perfmon with counters loaded

Configuring a set of counters on perfmon is pretty easy but saving them for reuse normally means having to create a "scheduled" trace, run it then open the data but here is a means documented to save settings for easier access : http://www.sqlservergeeks.com/blogs/AmitBansal/sql-server-bi/726/sql-server-open-windows-performance-monitor-with-your-default-set-of-counters