RSS

Monthly Archives: May 2011

Roll my own computer management software with WMI and VB.NET

Windows Management Instrumentation is a well documented framework for network admins. There are hoards of management software suites that use it and can give you great stuff right out of the box. Some of it is great and some is not so good. One simple piece of information I wanted was, how much free space do my sql server drives have? I wanted it in a spreadsheet so I could sort and start examining the servers that were getting low on space. We utilize several monitoring and management tools but there didn’t seem to be a good solution.

I avoided some possibly important tasks and wrote my own solution using WMI. Querying WMI is rather simple. It is a lot like querying a sql database. Here is an example:

Select Label, Name, FreeSpace, Capacity 
from Win32_Volume 
where Label  'System Reserved' and DriveType  5

If you start a new VB.NET project the only trick is to add a reference to System.Management. The core piece of code in my project is a function that I pass the computer and query into and get a management object back.

    Public Function QueryWMI(ByVal query As String, ByVal ComputerName As String)
        Dim wmiScope As New ManagementScope("\" & ComputerName & "\rootcimv2", New ConnectionOptions)
        Dim smoQuery As System.Management.ObjectQuery = New System.Management.ObjectQuery(query)
        Dim smoSearcher As ManagementObjectSearcher = New ManagementObjectSearcher(wmiScope, smoQuery)
        Return smoSearcher.Get()
    End Function

The next piece of code flips through the results. I chose to parse the results to a .csv file so I could simply double click and open in excel to do my slicing and dicing.

    Function wmidrivefreespace(ByVal ComputerName As String) As String
        Dim retstr As String = ""
        For Each oReturn As ManagementObject In QueryWMI("select Label, Name, FreeSpace, Capacity from Win32_Volume where Label  'System Reserved' and DriveType  5", ComputerName)
            Try
                Dim label As Object = oReturn("Label")
                Dim name As Object = oReturn("Name")
                Dim freesp As Object = oReturn("FreeSpace")
                Dim cap As Object = oReturn("Capacity")

                If label = Nothing Then label = " "
                If name = Nothing Then name = " "
                If freesp = Nothing Then freesp = " " Else freesp = (Double.Parse(freesp) / 1024 / 1024 / 1024).ToString("N2")
                If cap = Nothing Then cap = " " Else cap = (Double.Parse(cap) / 1024 / 1024 / 1024).ToString("N2")

                retstr &= ComputerName & "," & label.ToString() & "," & name.ToString() & "," & freesp.ToString() & "," & cap.ToString() & System.Environment.NewLine
            Catch ex As Exception
                System.IO.File.AppendAllText(DateTime.Now.ToString("yyyyMMdd") & "errorlog.txt", ComputerName & ex.ToString & System.Environment.NewLine & System.Environment.NewLine)
            End Try
        Next
        Return retstr
    End Function

For this to work there is a WMI service on the target machine that has to be started (it is by default). Also, inbound port 135 has to be open on the target machine. If it isn’t already open on all of your computers I would be surprised since so many other things use WMI.

If you visit MSDN and check out the classes http://msdn.microsoft.com/en-us/library/aa394084(v=VS.85).aspx you might be shocked at how much information you can gather.

There is a bug in the above code that is easily fixed. If you spot it, post a comment. The hint is: Make sure to wash your punk.

Advertisements
 
Leave a comment

Posted by on May 31, 2011 in .NET, Network Admin

 

Tuning With SQL DVR and Wait Stats

I have recently experienced a crossover between having knowlege in theory vs practice. It’s one thing to know DMV’s and profiler exist, and another thing to use them to identify the query that is causing performance problems. This falls in line with my favorite path to mastery of Learn One, Do One, Teach One.

Query tuning is a topic I will continue to come back to as I get better at it. I rate myself far from good as DBAs are concerned. I have not had that much experience, but I would like to share the steps I recently took to identify a process that is running rampant on one of my servers that holds only one vendor developed database.

We had an issue where the sql server CPU would shoot up and hang around 85-90% for the afternoon and return to normal (20%-25%) after normal business hours. I focused on a blocking issue that would come and go. SQLSentry help identify the blocking head which was a query that would run for about 30 seconds. It would block anywhere from 10 to 50 update queries for that time. This was happening a lot during the day. We found out that one of the parameters of the query was leading wildcard. This didn’t work out too well for the 30million row table.

We were taking traces at this time with a filter of > 1 second duration because of fear of adding too much overhead. We thought for sure the bad queries would be long running. We did this using the GUI (sql tv is bad) as instructed by the software vendor. Beyond the leading wildcard we assumed that there was also a similar query that would return far too many results. We identified two problems and sent them to the vendor. What we got back was a “this would be hard to fix/ educate your users not to do leading wildcard searches”. We were quite unsatisfied but then it was time for me to go to SQL Rally.

About a week after I got back I had taken a Friday off and come back to find out they upgraded the SQL server from 16GB ram to 32GB and 4v CPUs to 8v CPUs. It was running on its own blade server and seemed to be doing ok for the first day. Tuesday afternoon the same spiking CPU problem came back. We were unable to kill this problem with iron.

The first thing I did Tuesday was open up a query that I got from SQLRally by Eddie Wuerch. It examines the actively running queries and shows the wait stats. When the CPU was high, I ran this several times. I got rather discouraged when I didn’t see any of the leading wildcard or large result queries that we thought were a problem before. I then ran it several times in succession with a WAITFOR DELAY command in between each run. I found there was a query that was waiting on CXPACKET that had a short duration (100-200ms) but was executing every 200 to 300 milliseconds.

I decided to pull a few tricks out that I picked up from Grant Frichey at the SQL Rally Pre-Con. One tip was not to watch SQL TV and to do server side traces, what I call SQL DVR. I used profiler to select my events (RPC Complete and Batch Completed) and filters (database ID) then to export the trace definition. The other was to check the query plans for large pipes and compare Actual vs. Estimated rows. The last was to use profiler and import perfmon data (%cpu and sql server buffer cache hit ratio). That gives me the ability to drag a time slider bar and see what queries started running right when the CPU spiked. Also, to see if the same queries were running before the high CPU.

I very carefully setup a server side trace. I had my trace stop query ready to run in case the server slowed down. I was capturing the data to a local drive in 100MB files. We ran it for about 5 minutes and caught 300MB of data. I moved these files to my local computer and opened the first one. From there I chose to export the data to a trace table on one of our test servers. I ordered the query by CPU DESC and saw the same query that showed up in the wait stats query. I had heard this before but after looking it up I realized duration is in microseconds and CPU is in ms. Both time measurements I at first didn’t understand how CPU could be longer but then I divided it by 8 (number of CPUs) and got a number that was always less that duration.

I pulled this query out and put it into SSMS exactly as is and ran it with the “Include Actual Execution Plan” button selected. Sure enough I found several large pipes and three parallelism entities. One of the pipes showed estimated rows as 30 million and actual at 200 thousand.

Creating statistics on one of the columns might help this query but it is in the hands of the vendor’s developers. We created some neat pie charts showing how this one query was accounting for 98% of all CPU usage. I hope they stop the root cause of the problem which is running the same query 3 times a second with the same results.

 
Leave a comment

Posted by on May 29, 2011 in SQL Admin, SQL Dev

 

Car shopping with .bat

I’ve been test driving some cars and seriously looking for new ones. We stopped at a Ford dealer but wasn’t really impressed with the Fusion. After that, I drove a couple VW Jettas and it was a great ride. AutoTrader.com has a great search tool but what really got the attention of my local dealership sales person was when I showed him the price for a similar Jetta that was quite a bit cheaper and had less miles from an actual dealership. Salespeople aren’t too impressed with results from AutoTrader so I was looking for cars on a dealers lot.

What most car manufactures do is create a template website with inventory search capabilities. Then each dealership has an easy task of making their own site from that template. I get a map with all the dealerships in america when I go to: http://dealers.vw.com/dcc/dealerSearch.html

I was clicking each dealership and opening it in a new tab and checking prices. After doing this and realizing how inefficient it was I started putting my web development experience to good use. It was painfully easy to recognize all of my search parameters were in the address bar.

http://www.bettenvolkswagen.com/VehicleSearchResults?pageContext=VehicleSearch&search=preowned&search=certified&bodyType=All&make=Volkswagen&model=Jetta+Sedan&trim=All&minPrice=-2147483648&maxPrice=17000&minYear=2008&maxYear=2010&minMileage=-2147483648&maxMileage=30000&minMPG=-2147483648&maxMPG=2147483647

Problem was each dealer had its own domain name. Also, the HTML that came back would be difficult to parse. With a .bat file I could start each page with my HTTP search params. So I collected all of the dealerships I was interested in and lined up the commands.

start "vw1" "http://www.bettenvolkswagen.com/VehicleSearchResults?pageContext=VehicleSearch&search=preowned&search=certified&bodyType=All&make=Volkswagen&model=Jetta+Sedan&trim=All&minPrice=-2147483648&maxPrice=17000&minYear=2008&maxYear=2010&minMileage=-2147483648&maxMileage=30000&minMPG=-2147483648&maxMPG=2147483647"
start "vw2" "http://www.maplehillvw.com/VehicleSearchResults?pageContext=VehicleSearch&search=preowned&search=certified&bodyType=All&make=Volkswagen&model=Jetta+Sedan&trim=All&minPrice=-2147483648&maxPrice=17000&minYear=2008&maxYear=2010&minMileage=-2147483648&maxMileage=30000&minMPG=-2147483648&maxMPG=2147483647"
start "vw5" "http://www.vyletelvw.com/VehicleSearchResults?pageContext=VehicleSearch&search=preowned&search=certified&bodyType=All&make=Volkswagen&model=Jetta+Sedan&trim=All&minPrice=-2147483648&maxPrice=17000&minYear=2008&maxYear=2010&minMileage=-2147483648&maxMileage=30000&minMPG=-2147483648&maxMPG=2147483647"

The first attempt failed because chrome couldn’t keep up so I added a quick ping in between each command.

start "vw1" "http://www.bettenvolkswagen.com/VehicleSearchResults?pageContext=VehicleSearch&search=preowned&search=certified&bodyType=All&make=Volkswagen&model=Jetta+Sedan&trim=All&minPrice=-2147483648&maxPrice=17000&minYear=2008&maxYear=2010&minMileage=-2147483648&maxMileage=30000&minMPG=-2147483648&maxMPG=2147483647"
ping 127.0.0.1 -n 1
start "vw2" "http://www.maplehillvw.com/VehicleSearchResults?pageContext=VehicleSearch&search=preowned&search=certified&bodyType=All&make=Volkswagen&model=Jetta+Sedan&trim=All&minPrice=-2147483648&maxPrice=17000&minYear=2008&maxYear=2010&minMileage=-2147483648&maxMileage=30000&minMPG=-2147483648&maxMPG=2147483647"
ping 127.0.0.1 -n 1
start "vw3" "http://www.williamsvolkswagen.com/VehicleSearchResults?pageContext=VehicleSearch&search=preowned&search=certified&bodyType=All&make=Volkswagen&model=Jetta+Sedan&trim=All&minPrice=-2147483648&maxPrice=17000&minYear=2008&maxYear=2010&minMileage=-2147483648&maxMileage=30000&minMPG=-2147483648&maxMPG=2147483647"
ping 127.0.0.1 -n 1
start "vw4" "http://www.fox.vwdealer.com/VehicleSearchResults?pageContext=VehicleSearch&search=preowned&search=certified&bodyType=All&make=Volkswagen&model=Jetta+Sedan&trim=All&minPrice=-2147483648&maxPrice=17000&minYear=2008&maxYear=2010&minMileage=-2147483648&maxMileage=30000&minMPG=-2147483648&maxMPG=2147483647"

What I end up with is chrome open with tabs of all the cars I am interested in. I’d like to parse the HTML and save the results so I can be notified of changes, but that would take too long (lazy).

 
Leave a comment

Posted by on May 26, 2011 in Uncategorized

 

SQLRally Recap for the boss

“What’d you learn?” caught me a bit off guard and I joked about scanning the 17 pages of notes to send up. My boss asked me for a recap of the event to make sure the appropriate people know great value was added. I highly recommend you do this if you want to get future events paid for. Make sure something like this ends up in the hands of the decision makers. Keep in mind they may not even know SQL Server exists, let alone its where all the valuable data is kept.

**********************

PASS is the Professional Association of SQL Server. There is a yearly summit in the fall that has been a huge success for years. This was the first year of trying a shortened version of the summit which was called SQLRally. (3 days instead of 5 days)

The focus was instructional sessions given by community elected speakers. There was a menu of sessions to attend including the categories of Development, Database Administration, Business Intelligence and Wild Card. I have 17 pages of notes from these sessions that I plan on putting together in a presentable format for my peers.

The highlight was a 5 minute lightning talk by Grant Fritchey (http://www.scarydba.com/ ). He gave a scary performance in a high volume, intense manner. The message was just backing databases up isn’t good enough, and you need to test your restores.

Each day went from 8:30 to 5:30 and also had planned after hours networking activities. We learned immensely valuable information about SQL server and all the third party products available.

*************************

The key benefit is making connections at events like this. This way, as a team we can all learn SQL Server in practice and not just theory. Finding information from a source like BOL, Blogs, Forums etc. doesn’t hold a candle to talking to someone who has already “been there done that”.

 
Leave a comment

Posted by on May 18, 2011 in PASS, SQL Admin

 

#sqlrally notes from Friday

New Reporting Features of SQL Server 2011 by Michael Davis

Posted online was a session by Randy Dyess who I know because he was onsite mentoring us on some data warehousing. I’m trying to get a server dashboard report going and a custom IIS log report but that is the extent of my need for BI. I do have to administer RS. When I walked by the session title was actually like Administering RS 2008 R2 which is a weakness of mine.

There was a great question (if i do say so myself) about integrated security vs. stored credentials. Also, someone else commented later on the pains of getting integrated security working, which I have felt myself. Mostly SPNs and delegation problems but thats another story.

Michael gave very demo oriented explanations that went very well. It caused my notes to be a bit scattered but that is fine with me.

A shared dataset is something that multiple reports can consume. Creating a pathway for DRY reporting.

We didn’t get into the report building but BIDS or Visual Studio to experiment with report deployment.

-setup data sources
-deploy datasets
-could also deploy whole bids project
-.rdl files are created

Data driven subscriptions
-have to share credentials
-many report delivery options
-path has to be a share
-creates sql agent job with GUID name (don’t change)

report logs are in the database but not very readable -> see codeplex SQL Scrubs

CONVERT(char(10), GETDATE(), 112) to append date to a report filename… just add it to your query

good idea to setup shared schedules ->monthly weekly daily etc…

Schedule caching
-can save a rpt snapshot for performance
-test different caching rules

Showed some report security and that you can break inheretance, and then revert back to parent if you need to

For users you will want to enable myreports, use SSMS/with RS connection

***************************************************

SQL Server Troubleshooting via DMVs by Steve Schneider

Definite highlight of the week. I’ve seen good ones and duct taped some of the DMVs together myself but Steve has definitely put a “nujakcities certified sweet” stored procedure together.

He gets a very thorough snapshot of a server, or multiple servers if you have CMS setup.

Use process explorer to identify system processes and their resources

the SP is granular to the table, query text, even pointing out APIs and % of table that is in memory

The rest of the notes for this will come later once I get my head around the code

***************************************************

Evaluate your daily checklist against 100+ instances of SQL Server while you get a cup of coffee by John Sterrett

John covered mostly Policy Based management features. One of his key points was a hint of professional development to create a morning checklist. Then automate it.

Use CMS and register servers. Store the policies on this server and evaluate them on all other servers

Use powershell’s ISE to script against CMS

the EPM framework is what SSMS and Powershell use to interact with the servers, this is downloadable

Use PBM for mirroring because SCOM not so good

***************************************************

Deadlocking for Mere Mortals by Jonathan Kehayias

Yet another weakness of mine. The concept I avoided because my thought pattern was… get the queries running faster and they won’t run into each other, for now.

Jonathan started with a joke like fact that nolock will fix all dead locks. What made it hilarious was how terrible an idea this is to apply nolock.

– if you have similar transactions in your environment, make sure they work with objects in the same order
– Majority of deadlocks are a bookmark lookup deadlock, caused by clustered key and nci contention
— spid grabs lock on clustered index for update other spid grabs lock on nci for select

TFlag 1222 will start logging deadlocks
-sql server log can have all the info needed to resolved

Profiler “Deadlock graph event” will show on SQL TV the deadlock image.
-.xdl files are deadlock files that can be opened in SSMS

Read committed snapshot can help, but buyer beware, will increase tempdb utilization

Isolation level of serializable will take range locks which will block updates into that range

***************************************************

Zero to OLAP Cubes in 60 Minutes by Adam Jorgensen

download the contosoDW and open up a new BIDS project
-import from a deployed project (nice feature)

Work the way down the folders on the right setting up elements of this AS project

Cleanse the data with:

-Friendly names (in properties)
-named calculations ( turn M into Male )

don’t use named queries

Cube creation time
– use existing tables
– don’t use gen tables

New dimensions
– set attribute

Deploy to the AS server consume with Excel Data Connection Wiz and bazinga…

***************************************************

Fundamentals of SQL Server Internals by Jeremiah Peschka

What a splendid speaker choice considering I was reading his bosses co-authored internals book on the flight down.

Jeremiah walked the crowd through the engines response to our queries.

after networking protocols comes parser
-this gives a much better error in 2008 than it used to

then the optimizer (cost based with secret sauce algorithms)
-select * is not optimized
– can view QP to see optimization level
– multiple passes but only if not trivial
– user defined functions don’t have plans

Storange engine
data access methods
-moves data from/to mem or disk

forcing lock levels will disable lock escalation

locks use significant amount of memory

once scheduler per CPU

***************************************************
And brain has checked out to to disney world. I noticed there was a damn cd of the TOP PASS sessions… yay more homework.

 
1 Comment

Posted by on May 15, 2011 in PASS, SQL Admin, SQL Dev

 

#sqlrally Thursday notes-a-plenty

The Dirty Dozen: PowerShell Scripts for the busy DBA by Aaron Nelson

I learned a lot at this first session on Thursday but that was mostly because I didn’t have much up there to begin with when it comes to powershell. The syntax reminds me of perl and makes me want to vomit. I had been avoiding it until I heard about the vmware scripts released and the extensive sharepoint administration scripts that are written and available.

Aaron first made it clear that ps isn’t quite finely tuned for SQL yet. So here are my notes

Get SQL PSX: sqlpsx.codeplex.com

The PS development environment is ISE, from here you can run and debug.

Profiles are important and portable

Stick to a verb-noun naming convention

Get-Help works for most commands

Get-Member

$vars are typeless :/ and can be defined inline

Cmdlets are .net code addins

SQL servers can be added and are represented like drive paths

Specify “default” or the instance name

Format-table –auto is useful when viewing results

-force will show all the information which some is randomly hidden from users

Tip: ctrl+shift will run as admin, need elevated privs for service control etc…

Can save profiles to share and import them automagically

Ps sends email with send-mail

*************************************************************************

Troubleshooting Performance Problems by Reading the Waits by Eddie Wuerch

This was probably one of the most valuable sessions I attended all SQLRally. One of our worst performing systems is our datawarehouse, but we have developers that have access to the SERVER STATE. They have handled the wait monitoring which has caused me to delay learning about this topic. The value set in when Eddie told us that this is what he dives into first when he gets the 3am call. I have looked at waits in activity monitor but never understood them. I attempted to translate BOL to English but failed. Eddie did an excellent job at this translation and included common problems with their fixes.
One of the keys is signal wait time, this is CPU wait after the thread has acquired all of locks and other resources. Subtracting signal wait time can help you focus on the database waits. Eddie didn’t mention this but I connected the dots from his explanation and what a co-worker has said months ago.

LCK_M_X extra blocking

Pagelatch – hotspotting (update counters records), lots of #tables
These occur in mem

Can identify the wait resource (##:####:####) dbid:fileid:pageid. Once you get the page you can do DBCC Page(Pageid) and find out what might be on this thing.

Trace on 3604

PageIOLatch – disk waits, usually always a majority of waits because this is the slowest component

WriteLog – tranlog problems, maybe isn’t on right drives or you have VLF problem (I’ve seen 3million vlf’s before, VLF problems might be seen when a database takes a long time to recover after a reboot)

Resoruce Semaphore – mem issue

Sleep_bpool_flush – mem issue, lots of dirty pages being flushed

Sos_shced_yield – CPU

ASYNC_NET_IO – network problems

********************************************************************

Wesley Brown has an awesome and hilarious session on storage systems. I dived out of this one because it is not a weakness of mine. I have troubleshot SAN slowness and identified bottlenecks in HBAs. I did get the opportunity to chat with him in the birds of a feather session. Great guy and lots of gut busting jaw dropping stories from his IT vast experience.

I dropped into the DEV session Avoid common Mistakes in T-SQL programming by Alex Kuznetsov. He has a slight accent but the guy is a machine. Incredibly knowledgeable and showed me mistakes in a very organized fashion which I appreciate.

When a query has multiple where clauses they do not evaluate left to right. The optimizer picks which order to evaluate these clauses.

Mistake: select Name, DOB from tblCust where if ISDATE(DOB) and Cast(DOB, varchar(20))

– The cast would fail because it could be processed before the isdate
– To fix this common mistake use CASE WHEN ISDATE(DOB) then cast(DOB)

The settings that are defined can change query results. Such as English date conversions

Queries do not have a default order. TOP n queries can return different results. Index changes can cause code to have issues because that defines how data is laid out.
– To fix use the order by cause

TYPE conversions are common errors because they work with the tested values but production values can be larger or more unique cases

Small int would overflow if the value was 40,000. Varchar(5) would concat (awesomesause) to (aweso) without an error

Small date has rounding inconsistancies, close to midnight might round up to next day (or year on 12/31)

Be careful on the definitions inside of stored procs, same issues. Also for SPs, explicity specify which var you are passing in to the SP, because order can change.

Decimal rounding has strange results,
– To fix ABS(@dec-)

Select is faster that SET

Using decimal variables for loops
– SET_XACT_ABORT_OFF

Always use aliases when using multiple tables

Alex gave some great examples of concurrency issues that arise because testing often lacks multiple users

A banking example was used with two query windows or “users” BEGIN TRAN on one side and before the first was COMMIT then second tran would attempt to aquire locks but would fail with deadlocks because the resource was accessed in different order.
– Possible fix would be to set read committed snapshot isolation on, this way writes don’t block reads but you have a lot more tempdb activity.

********************************************************************

I then attended Jennifer McCown’s session on T-SQL Code Sins: The Worst Things We Do to Code and Why. Jen’s auctioneer style of presentation keeps you on your toes. My main takeaways where:

– NamingConventions: use good names. I laughed on the inside because DB2 on mainframes are limited to all caps 8 char names which is deciphered by a third party product to make useful names.
– GUID primary keys are a waste of valuables space.
– No FKs for performance (bad idea)
– Can query referencing_entities for cascading deletes
– GO n will run queries “n” times
– Don’t repeat selects, use one query to get all the data needed
– No docs or testing

********************************************************************

I planned on going to another dev session but was a bit burnt out on t-sql. I felt a responsibility to go to the only security session in both days of sessions. The session was Data Encryption and Key Management in SQL by Said Salomon.

R2 generates a self signed Service Master Key and there is one per instance and is stored in protected memory(? Was a bit confused on this)

BACK THE SERVICE MASTER KEY UP!

Said discounted using Asymmetric keys because of 100x performance hit.

The Service master key is used to encrypt sub keys. These sub keys actually encrypt your data and different algorithms can be used when generating these keys.

There are encrypt and decrypt functions that turn data into varbinary data. The user running these functions needs VIEW DEF and other key privs to de/encrypt data. Create a role for this priv

Can regenerate the SMK without breaking child keys because SQL manages these keys.

The child keys are stored in the database

********************************************************************

I hopped over to the lightning talks instead of the DR session I had planned on going to. Again, I made the correct choice. Exactly what I needed was a thrilling 5 minute Scary DBA rant about backups and restores. Grant was spitting mad and describe lazy DBAs who do not fully test their restores. I am one of those people. I have done restore testing but I need to do this all the time. I have luckily avoided RGE’s or “resume generating events” but luck will no longer be a factor going forward.

Jen also did another auctioneer style 5 minute intro to T-SQL. This was very impressive and a good refresher.

The professional development speaker also had great advice on how to become a lynchpin in your organization.

********************************************************************

All of this information is trumped by the value of networking with my peers and mentors. A solid team is far greater than any individual.

 
Leave a comment

Posted by on May 14, 2011 in PASS, SQL Admin, SQL Dev

 

#sqlrally notes from Grant Fritchey’s pre-con

Baseline baseline baseline – collect system counters to verify a baseline performance metric so future problems are easier to debug

For memory important counters are:
Pages/sec – when windows is under memory pressure pages will be higher than 50
buffer cache hit ratio – OLTP should be > 90 but reporting or OLAP might be skewed
Page life expectancy – watch for flushing events

SSMS -> MGMT -> Data collection sets can gather and offer pre-caned reports

Wait Stats and Queues are most important – use DMOs to collect this data constantly

Profiler Tracing: don’t use GUI on prod (no SQL TV newbs)
-Traces can be exported to script server side traces which are much better

After a trace file is created open it and click the “import performance data” button to select time frames of both performance data sets

Microsoft RML utils http://www.microsoft.com/downloads/en/details.aspx?FamilyId=B60CDFA3-732E-4347-9C06-2D1F1F84C342&displaylang=en

automate reporting off trace files and perfmon data

DBCC freeproccache (NO PROD) forces all recompiles for testing

sys.dm_exec_query_optimizer_info

STATS
-See header of DBCC show_statistics for age of stats
-plan re-use doesn’t update stats
-update stats with full scan intensive
-SP_updateStats

INDEXES
-dmv physical stats
-foriegn keys and referential integrity help the optimizer (better performance)

Execution Plans
– %s aren’t all that accurate
– Pipe size counts data flow inbetween operators
– element properties contain estimated vs. actual row counts – if off could be stats prob
– far left most element properties gives you the grade of the plan
– different plans for same queries could mean SET operations are different
– go into scalar operator properties to see the logic that is being performed
– check execution counts on loops (1 row return isn’t always good)
– can view parameter values in properties

Inserts
– constant scans are space holder for other operators
– multiple seeks could be checking for constraints

If optimization level is FULL focus first on other queries

covering indexes can remove need for costly key lookup

Security to DMO’s is granted by VIEW SERVER STATE and VIEW DATABASE STATE

sql_handle, plan_handle, and session_id are unique for joining dmo’s

these dmo’s contain aggregate data which is good for last 6hrs
-dm_exec_query_stats
-dm_exec_procedure_stats

[[[[DTA BAD]]]]

common problems
-slow queries
–check for large pipes
–check stats

parameter sniffing is good
-can cause fairly rare problems when data is skewed
—can use Optimize for commands

C.Column *2 = 240 <—-bad query
C.Column = 240/2 <—–good

TVP's report 0% in plan when they really do add cost, tune the query without the parameter

Profiling stmt completed can be very costly but includes more fine grain data
Profiling showplan xml is very cost but very convenient

Hints are bad: but can be used as last resort
– Plan guides
–sp_create_plan_guide — use with exact stmt (no spaces)
–can point a plan guide to an exact plan

 
Leave a comment

Posted by on May 12, 2011 in PASS, SQL Admin, SQL Dev

 

#sqlrally

Hasn’t even started yet and I have found a ton of value in this conference. I have to get some good sleep because I have a full schedule until I get back home.

On the plane I re-read the first three chapters of SQL Server 2008 Internals and troubleshooting. Tomorrow starts the actual conference with:

Query Performance Tuning, Start to Finish
Grant Fritchey

Probably going to BWW for an after hours networking event.

Thursday starts the sessions and these are the ones I have chosen.
The Direty Dozen: powershell scripts for the busy DBA
Troubleshooting Performance Problems by reading the Waits
Understanding storage systems and SQl Server
T-SQL Code Sins: The worst things we do to code and why
Writing faster queries
DBA Disaster Recovery Techniques to keep handy

Dinner plans are open, maybe back to bahama breeze because the chicken kabobs were awesome.

Friday has more sessions:

The BI DBA
SQL Server Troubleshooting via DMV’s
Evaluate your daily checklist against 100+ instances of SQL Server while you get a cup of coffee
Deadlocking for mere mortals
SQL Tricks
Fundamentals of SQL Server Internals

 
Leave a comment

Posted by on May 11, 2011 in PASS

 

Automating SQLIO testing

I’ve bloged about SQLIO a bit but never focused on it. SQLIO is a tool that creates disk activity and is useful when testing performance of a disk subsystem. When you run it you get some results like these:

C:UsersnujakDesktopSQLIO_old>sqlio -kW -t24 -s30 -o20 -fsequential -b4 -BH -LS -F1file1lun1th.txt
sqlio v1.5.SG
using system counter for latency timings, 2740654 counts per second
parameter file used: 1file1lun1th.txt
file C:testfile.dat with 1 thread (0) using mask 0×0 (0)
1 thread writing for 30 secs to file C:testfile.dat
using 4KB sequential IOs
enabling multiple I/Os per thread with 20 outstanding
buffering set to use hardware disk cache (but not file cache)
using specified size: 10240 MB for file: C:testfile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 24246.70
MBs/sec: 94.71
latency metrics:
Min_Latency(ms): 0
Avg_Latency(ms): 0
Max_Latency(ms): 47
histogram:
ms: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+
%: 100 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

There are some important items in these results.

1. The date and time of the test (we’ll have to get that somewhere else
2. The command that was run, a trick is to properly name your parameters file so those are included in the command.
3. The IOPS
4. The MBps or bandwidth
5. The Avg Latency

The first thing you’ll want to do is make a good set of parameters files. Create small test files to test disk caching and large (200GB for some SANs) to test actual spindle activity. Datawarehouses often have a large number of LUNs that should be tested simultaneously. So have -F1file1lun1th.txt be a test and then -F1file12lun1th.txt be a test. You might want to test each LUN individually and also walk the way up to find the correct number of LUNs that max out your HBAs.

Make a batch file that has a series of SQLIO calls. They don’t have to be in any particular order because we will be sorting that out later. Then, is what you do is make another batch file that calls your first batch file and pipes the output to a text file. This text file will contain the above results over and over again for each test. To be scientific you should put a wait inbetween each call and also test each command at least three times.

Once that is done you setup your parsing program with … none other than my fav.. VB.NET.

Module Module1

    Public CurrentDir As String = System.Environment.CurrentDirectory &amp; ""

    Public OutputFile As String = CurrentDir &amp; Format$(Date.Now, "yyyyMMddHHmmss") &amp; "output.csv"

    Public TempDir As String = CurrentDir &amp; "Results"

    Public dir_info As New IO.DirectoryInfo(TempDir)

    Sub Main()
        parseresults()
    End Sub

    Function parseresults() As Boolean
        Try
            Dim file_info_ar As IO.FileInfo() = dir_info.GetFiles()
            Dim file_info As IO.FileInfo
            Dim line As String
            Dim contents() As String
            Dim itemcounter As Integer = 0
            Dim csvfile As String = "time,cmds,iops,bw,late" &amp; System.Environment.NewLine

            For Each file_info In file_info_ar
                contents = System.IO.File.ReadAllLines(TempDir &amp; file_info.Name)
                For Each line In contents
                    If line.Contains("C:SQLIO&gt;sqlio") Then
                        csvfile = csvfile &amp; file_info.Name &amp; "," &amp; line
                    End If
                    If line.Contains("IOs/sec:") Then
                        csvfile = csvfile &amp; "," &amp; line.Substring(8, line.Length - 8)
                    End If
                    If line.Contains("MBs/sec:") Then
                        csvfile = csvfile &amp; "," &amp; line.Substring(8, line.Length - 8)
                    End If
                    If line.Contains("Avg_Latency") Then
                        csvfile = csvfile &amp; "," &amp; line.Substring(17, line.Length - 17) &amp; System.Environment.NewLine
                    End If
                Next
            Next

            System.IO.File.WriteAllText(OutputFile, csvfile)

        Catch ex As Exception
            System.IO.File.AppendAllText("exceptions.log", ex.ToString)
            Return False
        End Try
        Return True
    End Function
End Module

I decided not to go directly to SQL with this because a .csv can be opened in Excel. Excel makes it very easy to graph the results. Also, csv’s pop into SQL quite easily if you want to query the data. SQLIO definitely has its place even though it doesn’t simulate real world activity. If you find your bandwidth limited to a solid 1Gbps or you can identify easier which piece of hardware to upgrade.

 
Leave a comment

Posted by on May 9, 2011 in .NET, Network Admin, SQL Admin

 

Windows Updates

Keeping your software up to date is the number one way to prevent security issues.

Patching a large number of computers is a painful process. When it comes to servers, there are two automatic methods, WSUS or simply turning on automatic updates. These methods are great but it makes change control and testing difficult. The success rate for these can be low which usually turns this back into a manual process. You should have some form of alerting when these fail or you may go months or a year without getting the patches you need. Or even worse, an update/reboot could take an application offline and you don’t find out about it until the users are calling.

RDPing into the server and check for updates is the way I have been doing it for a while. When it comes to SQL server this is usually pretty easy as long as you stay on top of the updates. Microsoft patches generally don’t break microsoft products, its the 3rd party vendor apps that you have to watch out for. Once a month, no more no less, is a good rule of thumb to apply updates. No more because then your database is coming offline too frequently. No less because microsoft highly recommends critical security patches be installed within 7 days and you will get severely dinged on scans if you are 8 days old on a critical.

If you have a strict testing policy you have to be careful about not applying untested updates to production. This gets tricky because during the week or two that it takes to test more patches could come out and be available to install in production. If you have 100 servers its simply unrealistic to go around with a list of KB’s get everyone installed on the correct dates.

That said its unrealistic for one person to keep 100 servers patched manually unless that is all they do… and work overtime. The most time consuming part is when patches fail. I’ve started to get in the habit of migrating databases off these server because its faster than troubleshooting windows patches.

To further increase my ability to keep servers patched I’ve started using some .NET code and other auditing mechanisms so I can target the worst offenders first. Using WMI you can query the last reboot time, service pack level and build number of a windows machine. In addition to that, I get the free space on the C: drive to make sure my updates will go smoothly. I also pull data from network vulnerability scans and merge this information together to create neatly ordered a list.

Remember, a network is only as secure as its weakest link.

If you have problems installing an update there are a few things you can try.

1. download the redistributable: search for the specific KB and make sure you download the correct one for your bitness, always try to run as administrator
2. verify you have the .net framework prereqs: i’ve had some success downloading an earlier framework versions and retrying patches
3. download the system readiness tool: this will spin through various system checks and give you a report
4. check the update history: maybe the current update is failing because a previous one partially completed
5. check the event log after a failure: you might get tipped off, also googling event log errors usually comes up with some good info
6. update windows update agent: in 2003 there was “windows update” that should be updated to “microsoft update” to pickup SQL or other MS product updates. In 2008 there is an OS shortcut to this agent that should update itself, but sometimes does not do that successfully.

7. packup and move to a new server…

 
Leave a comment

Posted by on May 7, 2011 in Network Admin