Copenhagen is the location for this free event about SQL Server. My session this time is about LSN and the different backup combinations. How you can mess things up by yourself and how to avoid it…
ASYNC_NETWORK_IO for dummies
December 17, 2012
SQL Server C#, performance, SQL Server, wait stats Leave a comment
I often come across “less than optimal” applications, when I do performance tuning. You know, that kind of application that are kind of responsive, but the main issue is the slowness. Everything about the application is sloooow…
What is one of the signs that you might have a slow client application? (Disclaimer: Slow = Doesn’t consume SQL Server data in zero or less milliseconds. All applications are more or less slow, but abnormal slowness can make users mad.)
The magical query:
SELECT *
FROM sys.dm_os_wait_stats
order by wait_time_ms desc
According to Guy Bowermans article, the statistics lifetime is (about) the time of the SQLTRACE_INCREMENTAL_FLUSH_SLEEP.
I now did some illustrative tests: I wrote an extremely simple console application, selecting the ID column from a table with ~1000000 rows.
Code: (C#, Console project)
static void Main(string[] args)
{
SqlConnection con = new SqlConnection(“Data Source=.;Initial Catalog=testdata;Integrated Security=SSPI;”);
SqlCommand cmd = new SqlCommand(“SELECT SalesOrderID FROM [Sales].[SalesOrderDetail]”, con);
SqlCommand cmdclearstat = new SqlCommand(“DBCC SQLPERF (‘sys.dm_os_wait_stats’, CLEAR);”, con);
con.Open();
cmdclearstat.ExecuteNonQuery();
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
int a = rdr.GetInt32(0);
//Console.WriteLine(rdr.GetInt32(0));
}
con.Close();
}
When I ran this code, the SQLTRACE_INCREMENTAL_FLUSH_SLEEP was 4000 at the time of the application end. The ASYNC_NETWORK_IO was at 122, giving us a wait ratio of 0.03.
The “problem” resulting in the real issue is: My application takes 4 seconds to run – without any feedback to the user. Not many users are comfortable with that. The “solution” is to uncomment the Console.Writeline() line. (I actually also removed the int a… line, in order to reading the same amount of objects.)
Results was amazing! I could follow the progress of the application as it looped through my rows. When the application was ready, the SQLTRACE_INCREMENTAL_FLUSH_SLEEP had reached 120024! Yes that’s correct! Two minutes of wonderful interaction 🙂 And the ASYNC_NETWORK_IO (the time SQL Server waited for my application to get the next row) was 120201. That is a ratio of 1.0015.
The server indicates that a “slow” application has made the server wait a lot! So what should you do?
Sometimes you have to choose between the unresponsiveness and the slowness, but sometimes you can get the best of the two worlds. You can try to update the screen every 10000 row, or even every 50000 row. Even with the code overhead, this application completed in virtually the same time as the blazing fast one. The difference was ASYNC_NETWORK_IO waited for 160 ms, resulting in a 0.04 ratio. Still acceptable, and the application (randomly) outputted data, making it seem more responsive.
“Optimal code fragment” replacing the while(rdr.Read()) statement:
int count = 0;
while (rdr.Read())
{
int a = rdr.GetInt32(0);
if (count % 50000 == 0)
{
Console.WriteLine(a);
}
count++;
}
Hopefully you’ll be able to identify some issues with these samples.
Certification Rush
December 5, 2012
SQL Server certifications 1 Comment
Just hours ago I did my seventh certification in four days. Mr Brain is ready for some Christmas. The guys at Informator, Malmö sponsored my certification frenzy (thanks Acke) so I could do two certs per day, between 8 and 10, before the full time consulting started.
Result was OK. Not perfect scores, but still passing score on all 7 SQL Server 2012 certifications. Tonight I’ll celebrate with friends and tomorrow it’ll be business as usual 🙂
So it’s official
November 23, 2012
General, SQL Server, Uncategorized Leave a comment
I’ve been working for myself for more than eight years now. I like it. The fact that I’ m my own boss. I decide what to do and what to buy 😊. However sometimes you need co-workers. You know, the persons you can talk to and ask stuff without the risk that they will think you are strange and will never hire you again…
I got the opportunity to work for SolidQ. A really “front line” kind of company. I will still be in business as usual, but I can also be reached at my new company. I havre already found this arangement useful and fun, and and can’t wait to contribute to the SolidQ community.
SQL Rally done…
October 11, 2012
SQL Server, Travel Leave a comment
OK, so I survived.
My talk was about query plans and statistics and had a moderate 200 level. There was a lot of administrators listening, so I really hope they got their moneys worth out of it. I’ve heard a couple of comments, mostly positive so I’m over all content.
I realized that one hour is only sixty minutes, and those minutes are not slowing down when I need to have more time. I had material for around 70-75 minutes and thought that the stress level I’d be in with 100+ attendees would make me talk fast and forget stuff, but no such luck 🙂 I had to compress the statistics part a lot, but I think I managed to make all of it to make sense in the end.
However, the big success indicator will be when/if I’ll be invited to another conference… Good things come to those who wait 🙂
I was a bit surprised – and scared…
August 24, 2012
Exactly! My session was approved at SQL Rally Nordic.
I’ts not that I haven’t done speaking at conferences before, dude, I’ve even sung at weddings and at concerts. But SQL Rally! It’s an event where everyone knows a lot. I bet there are people there, knowing tons of stuff more than I know. And they don’t even speak.
If you are looking for an event where you learn a lot of things about SQL Server, this is it. You learn from the sessions, from the discussions – and most of all, you learn stuff when you eat lunch with your buddies at the conference.
MCM Lab exam, attempt two
August 19, 2012
SQL Server #mcm Leave a comment
Failing is good. At least I know what’s expected from me now. I can imagine the re-test being similar, but not the same, so memorizing parameters and stuff would mean nothing.
I started to study instead. Study meant even more advanced consulting, trying to get customers to do things I need to test, which is much harder than you can imagine, because customers often know what they want to have done… I also started to look at the MCM Videos. They were a great help in keeping track of what I’d revisited and what I still had to do. iPad with a video player, and long train rides was my rescue here 🙂 Also reading Paul Randals and Brent Ozars blogs took some time here. (Actually the entire sqlskills.com and brentozar.com are well worth a visit)
The last of April arrives in giant leaps. I took another long train ride, looking at videos and doing some SQL-stuff on the train. The two days (sat-sun) before the exam was well spent in a mix of lab/BOL and walking around in Berlin.
Exam day: Even though I was well prepared for the exam, slept well and expected 6.5 hrs of brain mashing activities, I was really empty when I got out. First feeling was that I completed all test parts this time. But then the second guessing started… I should have done that instead. Ahhh, I forgot to check that statement – or did I? – And the second waiting period started.
It took until July 4 before I got the mail this time. The title was something “Congratulations” and I had got the perfect start of my vacation. Croatia, you are going to be visited by a fresh MCM!
MCM Lab exam, attempt one
August 10, 2012
SQL Server #mcm Leave a comment
Back in Berlin. New Years Eve!
First I went to the big party at Brandenburger Tor. A lot of music and wurst, no beer. Brain must be fit for the certification.
This time I had actually done some preparing. First the obvious prepairing – reading BOL and consulting. Then also some installing of clusters, optimizing and testing aspects of T-SQL. I didn’t really know what to expect, but I expected worse than last time.
Certification started. 6½ hrs later the certification ended – and I wasn’t done. That has NEVER happened to me. I usually do a 2 hr cert in 45 minutes. Now I wasn’t done!
Exiting the building, I walked. Just walked. A few hours later, I was at the hotel. I think I must have ate something, but I don’t know what. It felt bad. I know I failed some of the lab, but… No, I didn’t think I had passed. A few days later I got the verdict: Fail! – but not by much. Much less than I would have expected from the feeling.
After sorting out my mind, I was also a bit amazed. Imagine squeezing so much hard and harder knowledge to be performed (as in artistery/performance) in that relatively short time. Next time I have to work faster, think smarter and be “expertier”.
First attempt of SQL MCM theory
July 28, 2012
SQL Server #mcm Leave a comment
In august 2011, I went to Berlin, that happened to be the closest MCM certification center for me in Sweden. I didn’t know what to expect from this certification at all. Somewhere I hoped that working with SQL Server from 1994 and teaching Microsoft courses since 1997 would help…
Certification started at 9:30 in the morning, so I went there a day before, to be able to chill in the hotel bar – or nervously trying to sleep 🙂
When the (food- and drink-less) certification was over, I felt brain-picked! I really felt like someone had squeezed my head for knowledge. I took a well deserved japanese soup and the waiting begun.
Happiness!! In a few weeks I got an email that said I passed the first test. Now it was “only” the 6.5 hour lab to pass.
Mikael Wedham 