Simons SQL Blog

One of my most popular posts with over 10, 000 hits is how to enable full text when using LINQ to SQL http://sqlblogcasts.com/blogs/simons/archive/2008/12/18/LINQ-to-SQL---Enabling-Fulltext-searching.aspx, core to this is the use of a table valued function. I’m therefore interested to see that Entity Framework will support table valued functions in the next release for more details have a read of the efdesign blog http://blogs.msdn.com/b/efdesign/archive/2011/01/21/table-valued-function-support.aspx.

I will be doing a training day at SQLbits in April on Performance Tuning and Query Optimisation.

This is the outline for the day. Its going to be an intense day, I look forward to seeing you there. To register go to http://www.sqlbits.com/information/registration.aspx. Places are limited so make sure you register soon.

Outline of the day.

Most database performance issues are due to a combination of bad queries, bad database design or poor indexing. All of them are related to each other. In this session we will look at database performance from identification through to resolution. This will include how to identify poor performance, how to dissect a query to understand what it should do and what indexes it should requires and finally how to resolve the performance problem by query rewriting, altering the database design, adding indexing and even not using the database.

During the session we will look at some of the common performance problems, like use of Common Table Expressions (CTEs), Temp tables and table variables, the OVER clause and parameter sniffing.

The agenda for the day is as follows

Identifying performance problems

• Adhoc analysis
• Trend analysis
• Tools to use, DMVs, Profiler, Extended Events, Clear Trace
• Best practices

Diagnosing cause of performance

• Looking at resource usage, IO, CPU and memory
• Identifying contention locking/resource

Dissecting a query and its query plan

• How to work out what a query should do
• How tables are filtered using the “filter funnel process”
• The importance of seek predicates and normal predicates
• How are tables joined together, Hash, Merge and Loop joins
• Impact of statistics and parameterisation
• Understanding what indexes are needed for a query

Resolving performance

• Query rewriting
  • Key areas that affect performance including
  • Predicates
  • Common table expressions
  • Table variables and temp tables
  • Functions
  • Aggregations
  • Parallelism
• Database design
  • Normalisation
  • Common pitfalls
  • Partitioning should you or shouldn’t you
• Indexing
  • How to determine the correct indexes,
  • Missing index DMVs
  • Impact of indexing
• Not use the DB
  • When to push back to the application

After this session you will be able to analyse the performance of your database application, identify areas that can be improved, dissect the relevant queries to find the cause of the poor performance and resolve the problem by changing the queries, the database, adding indexes or getting the application to change.

To register go to http://www.sqlbits.com/information/registration.aspx

I recently announced the awesome line up for SQLBits 8 in which I mentioned Conor Cunningham.

Yes we have Conor coming back. Conor is the most popular SQLBits speaker ever.

Conor Cunningham is a Principal Software Architect at Microsoft on the SQL Server Query Processor Team.  He's worked on database technologies for Microsoft for over 10 years and is holds numerous patents related to Query Optimization and Query Processing.  Conor is the author of a number of peer-reviewed articles on query optimization techniques.  Recently, he authored a chapter for the book "Microsoft SQL Server 2008 Internals" on how the Query Optimizer works.

At SQLBits 6 he filled our largest room and more (http://www.flickr.com/photos/janed/4527758206/). You can see his SQLBits 6 session here https://www.sqlbits.com/Sessions/Event6/Inside_the_SQL_Server_Query_Optimizer.

Why not have a break and read some of Conor’s blog http://blogs.msdn.com/b/conor_cunningham_msft/

If you are serious about SQL Server then seeing Conor in action is an absolute must. So register for SQLBits 8 now before its too late. http://sqlbits.com/information/registration.aspx

We’ve got some really exciting things happening at SQLBits 8. I’ve been dying to tell everyone since I found out. The first is our line up from  Microsoft.

We mentioned in the announcement of SQLBits that the SQLCAT team would be back. Well thats just the tip of the iceberg.

Today we have announced that we will have the following speakers from the SQL Team.

Mark Souza – General Manager for SQL Server

Thomas Kejser - Senior Program Manager, SQL Server CAT

Lubor Kollar – Principal Group Program Manager, SQL Engine

Ewan Fairweather – Progam Manager SQLCAT

Conor Cunningham - Principal Software Architect on the SQL Server Query Processor Team

Ross Mistry -  Enterprise Architect for Microsoft

Richard Tkachuk -  Senior Program Manager for SQL Server specialising on large scale SSAS implementations

As you can see we are wheeling out the big guns, we hope you enjoy.

I’ll be following up this with a series of posts about each speaker.

This the largest representation from the SQL Team in the UK since I’ve been working with SQL.

If you are serious about SQL Server then coming to SQLbits is an absolute must. So register for SQLBits 8 now before its too late. http://sqlbits.com/information/registration.aspx 

Ever find that SQL Server Management Studio just won’t shutdown. This often happens if you log off and find that Management Studio is blocking the shutdown. If you click on Management Studio it just beebs at you.

Well the problem is likely to be the help viewer, and this happens more on new installs.

What happens is that the help viewer gets launched for some reason, but as a background task. However on a new install when the help viewer is launched it asks you if you want to be a surf dude or a desk jock, i.e. use the online or offline help. The problem, which I’m sure you’ve figured, is that because the help viewer is launched in the background you can’t see the form and so can’t click on it. This means it blocks SSMS from shutting down.

The answer

You have to commit mass murder.

You have to kill the dexplorer process. Not just once but more likely 4 times.

Once you’ve done that Management Studio should shutdown.

Resolution

To ensure it doesn’t happen in the future make sure you hit F1 before closing management studio and selecting your preference. This should mean that the help viewer launches and closes properly.

I believe this should not impact Denali as that uses a different help viewer.

The final set of SQLbits videos has been uploaded to the site. You can either watch online or download them.

Some highlights of sessions that have been uploaded are

Lies, Damned Lies And Statistics. Making The Most Out of SQL Server Statistics
attend this session to understand exactly how the optimiser decides on its plans

SSIS Dataflow Performance tuning
Need to eek out a bit more oomph from your dataflows. This session might be of some use.

I’ve been a fan of the the SayNoTo0870 website for ages. I hate it costing me money to phone a company for support of their product. Especially on a mobile phone where 0845 and 0870 numbers are v expensive.

So I’ve developed an accelerators for IE one allows you to find an alternative number using the company name and the other by using the original number.

You can add the accelerators by going to my downloads page.

To use the accelerator just select the company name, right click and navigate to the accelerator

Clicking on the accelerator will take you to http://www.saynoto0870.com  with the relevant search results.

Domain name resolution is a very common cause of delays when doing anything over the network whether its accessing a web page or connecting to SQL Server.

Everything uses DNS. TCP/IP works with IP addresses and not friendly names. When using names you have to have a way of resolving names to IP addresses and thats what DNS does.

If your DNS is wrong then you'll get delays and also potentially odd results. For instance if you have multiple DNS servers configured you may find that occasionally you end up with connections not working but miraculously it works the next time. This can be caused by one of the DNS servers not working correctly and thus you getting a wrong of no result.

How do you know whether you do have a DNS issue? Well for web pages fiddler comes to the rescue (Its an awesome tool  a must for any developer/itpro www.fiddler2.com ) 

In the statistics section of each download is a DNS resolution value . You want that figure to be as low as possible, anything > 100ms I would consider a concern.

For SQL connections you will have to resort to a network sniffer such as wireshark. Although where SQL is making a network connection during logon information may be available in the the ring buffers http://blogs.msdn.com/b/sql_protocols/archive/2008/05/20/connectivity-troubleshooting-in-sql-server-2008-with-the-connectivity-ring-buffer.aspx 

Additional tools are useful to help resolve issues, are ipconfig, Ping, tracert and nslookup. I’m sure you are aware of Ping and tracert, you could have a read of Ping-and-Tracert-explained-for-dummies .

Nslookup allows you to find out if a specific DNS server can provide you with the answer that you are after.

nslookup <domain name> <ip address>

will allow you to try and find <domain name> using the DNS Server with an ip address of <ip address>

To find your DNS servers use Ipconfig /all which will list them. However its is more than likely that the DNS servers you are pointing to are local to you enterprise, which means you don’t manage domains such as google, bbc and facebook. To get these you will either need a DNS server in your ipconfig that can give you the answer or your DNS servers will have to allow requests it doesn’t have to be forwarded to someone that does. This does however mean that in the event that your DNS server is configured incorrectly it might be forwarding requests to the wrong place.

Thats exactly what happened to me recently. I have two lines into my office in case one goes down. My internal DNS was only configured to forward requests to the DNS servers on one of the connections. When that connection went down due to a problem with a router it meant I got odd browsing experience. Pages would halfload, (each image, script is a different request that may get a bad DNS resolution).

In my case setting up my internal DNS with the correct forwarding servers worked. A small note you can add additional DNS servers to your ipconfig, I read an interesting article that really thats not good practice as all you internal DNS requests will also be going out to the external DNS service, and so is a security risk.

All in all if you think that is taking a long time to connect to a resource, then DNS might be a problem

Its the reports.

Take this for example, it is a report generated during a demo of Report Builder 3.0 by someone in MS marketing. She says things like “..and you can see easily how my divisions are doing”. No you can’t you’ve just got a bunch of numbers.

This is really the issue with reporting systems and especially with Microsoft reporting. The problem is that people think generating a report is about getting lists of numbers onto a page. Reporting services is great at doing that and its very easy, as the lady said. However is that report of any use.

No.

The generation of a good report, just like the generation of a good UI, or a good architecture is not easy. Anyone can develop a report, or a UI or put together a few systems but making them work well and have people use them is a different beast.

Reporting is only any good if people use it. I have worked on many reporting systems where users define what a report should be like, only so they can get the data, then realising it doesn’t answer the questions they have and so they get another report. This continues until you have loads of reports or the user gives up. Invariably in these situations the lack of good reporting means that users don’t use the reporting system.

With Microsoft Reporting Services you can deliver great usable reports, it just takes some time. Is that wrong, that it takes some time. No its not. Reminds me of the 3 little pigs. I am sure it took the last little pig a lot longer to build his house of brick than the ones made of straw or wood.

I wish Microsoft demonstrations would concentrate on how its possible to deliver great reports and not reports that take 2 seconds. I do believe there are two classes of reporting. Reporting and Analytics. There is a blurred line between the two, at what point do reports become analytics. The transition between the tow is essential and the problem with many reports, and an area that often causes problems. What often happens is that people try to provide analytics capability in the reporting designer. The problem with that is that analytical users often don’t want the complexity of a report designer. The other solution is to provide a reporting solution that allows for analytics. The challenge with that is that analytics need to be flexible and most reporting solutions don’t provide enough flexibility.

I like the idea of Crescent (whats coming for reporting services in Denali), it seems to address the analytics side of the situation. I do hope that there is an integration story between reports from Reporting Services and Crescent, in order that applications can generate reports and if users want to analyse data then they can do by going from the report into Crescent.

When you start doing reports, think how useful the report is, if you removed the numbers could you see whats going on?

What ever you do, when trying to implement a reporting solution, do not put a report like the one above in front of your CEO.

There are many that joke about developers always talking about webscale and needing to shard to be able to scale. In reality many systems, if not most, don’t need to be able to scale to numerous nodes because todays processing is so powerful. However in the cloud world where you don’t have 1 big box you have many little ones (instances) you need some way of sharding/federating/distributing data and load.

I’ve mentioned before of a PDC presentation on whats coming in SQL Azure, well they’ve put some more content out there including details on how to shard with the current Azure infrastructure.

http://blogs.msdn.com/b/sqlazure/archive/2010/12/23/10108670.aspx

The new stuff looks real neat and easy to use, but just because its not easy at the moment doesn’t mean its not possible, you just have to work at it.