I was recently working on a legacy ASP.NET application which was taking like 30 seconds to load a simple page with a Gridview.
The culprit was Linq to SQL lazy-loading combined with an inefficient architecture. By tidying that up I was able to get the page load time down to about one a second.
I could have stopped there but I thought it would be nice to see if the SQL performance could be improved further.
Step 1. Profile
Start SQL Server Profiler and create a new trace. Choose the "Tuning" profile.
If your database server has more than one active database on it, you will want to choose to filter activity to your database only.
Once the trace starts recording, go to your website and hit the page you are trying to improve. In my case it was a page listing Help Requests, so I hit that page and did a couple of searches. Stop recording the trace and save it. I called mine “help requests.trc”.
Step 2. Analyse
Start SQL Server’s Database Engine Tuning Advisor. It will automatically start a new Tuning session when you connect to your database.
Choose your trace file that you’ve just recorded (1), choose your database (2), tick your database again (3), and finally hit Start Analysis (4).
Oops, that’s not quite right. It’s reporting errors, "SHOWPLAN permission denied in database ‘DBTest’"
At first this didn’t make sense, because I’m a dbo so I have permission to do everything. But remember – we’re running a trace we recorded earlier. IIS’s application pool for the website is setup to use a specific account – COMPANYDOMAIN\IIS_APPUSER, and this account doesn’t have SHOWPLAN permission. So grant it:
GRANT SHOWPLAN TO [COMPANYDOMAIN\IIS_APPUSER]
Now run the trace again, and you should see the recommendations screen.
Step 3. Tune
As you can hopefully see, SQL Server recommends creating 4 indexes and a bunch of statistics, and estimates a whopping 92% speed improvement! Press the Save recommendations button in the toolbar to save those recommendations to a SQL script which you can review and tweak, and then run that script against your database.
The same colleague I was helping with their SQL install yesterday made another mistake. She mustn’t have set herself to be the db admin, because we’d get “Access denied” messages when she’d try to connect to her local db using SQL Server Management Studio.
Fortunately she found this script by Ward Beattie which fixes the problem. She only had to enter MSSQLSERVER as the SQL instance name.
I keep getting this error message pop up in Visual Studio – “A member of the db_owner role must use the Visual Studio database diagramming functionality in order to set up the required database diagramming objects on the SQL Server.”
Typically it would happen whenever I drag a table from the Server Explorer onto the LINQ To Sql designer dbml. The error displays, and then after that the Server Explorer window locks up with the Windows 7 hourglass aka the toilet bowl. I can keep using Visual Studio but I can’t do anything in Server Explorer so I eventually have to restart Visual Studio 2010.
I was a bit confused because 1. I’m a dbo, and 2. I’d already setup diagramming in SQL Server Management Studio 2008.
I eventually realised – the solution I’m working on has two DB connections in Server Explorer – my application’s main db for settings etc (which is the one I’m working with when the error is thrown), and another read-only external database which my application reads data from. That external db doesn’t have diagramming set up yet so I presume VS is complaining about that.
There are two workarounds – 1. ask a dbo to set up diagramming on the other db, or 2. remove the connection to the other db from Server Explorer.
I did option 2, and the downside is I have to re-add the connection whenever I want to update my dbml. Fortunately I don’t have to do that very often.
First, the good news. Data dude is now included in Visual Studio 2010.
If you get a message “You cannot write updates to the target when you compare the specified types of schema models” when you try and import your database into a data dude project, it’s because you’ve done a schema compare of your database against a “SQL Server Data-tier Application” instead of a “SQL Server 2008 Database” project.
UPDATE: you might also get this error message if you import the schema from a SQL 2005 database into a SQL Server 2008 Database Project.
DISCLAIMER: I’m new to SQL Server Full Text Search, so if what I’m saying here is wrong then let me know.
Sticking with the Knowledge Base project, we had to implement search. We decided to use SQL Server Full Text Search for this. Once the fulltext catalogs were setup, the query my colleague wrote looked like this:
SELECT [Key], [Rank]
FROM FREETEXTTABLE(dbo.Question, QuestionText, 'building consent form', 100)
The problem with this search is that it will return results which contain any one of the keywords. So if a user enters “building consent form”, a page only needs to contain building OR consent OR form and it will be included in the results. I call this an OR search. This reminds me of the early days of search engines, (AltaVista.com et al), which used to work the same way.
Back in the AltaVista days, if you wanted only pages with every search term you had to use AND between each search term e.g. [building AND consent AND form]. Nowadays you don’t need to type the ANDs; if you enter [building consent form] into Google, it will only return results which contain building, consent, and form. This is what I call an AND search.
SQL Server Full Text Search seems to be stuck in the late 1990s days of OR searches. As demonstrated above it will return a hit for any one of the search words. And there doesn’t seem to be an easy way to tell it to do an AND search.
To do an AND search with SQL Server Full Text Search, you need to query the CONTAINSTABLE.
SELECT [Key], [Rank], 1
FROM CONTAINSTABLE(dbo.Question, QuestionText, '"building" AND "consent" AND "form"', 100)
Because we’re querying the CONTAINSTABLE and not the FREETEXTTABLE we lose all Full Text Search’s nice fuzziness around the search terms, i.e. in the above example, Questions which contain “buildings”, “consent”, and “form” won’t be returned. To help we can do the following:
SELECT [Key], [Rank], 1
FROM CONTAINSTABLE(dbo.Question, QuestionText, '"building*" AND "consent*" AND "form*"', 100)
Stop right there
Another problem with this is stopwords, aka noise words (SQL Server 2005 calls them noise words, 2008 stopwords). SQL Server has a list of around 150 common words, which are ignored by the Full Text Search engine. e.g. about, after, all, also, an, and, another etc. You can view the English stopwords in SQL Server 2008 like so:
select * from sys.fulltext_system_stopwords where language_id = 1033
In my table there is a Question titled “What are building consents?”. If we search for “what are building consents” like so:
SELECT [Key], [Rank], 1
FROM CONTAINSTABLE(dbo.Question, QuestionText, '"what*" AND "are*" AND "building*" AND "consents*"', 100)
because “are” is a stopword (and so is “what”) we won’t get any results!
There is a workaround – to wipe the list of stop words! The problem with doing this is that the list of stop words is setup server-wide on the SQL Server itself. In our production environment other applications are using the same SQL Server instance (but of course we have our own KnowledgeBase database), so this wasn’t an option.
Then the customer added a new requirement – if a Question contains the exact search phrase then that should be returned first. Adding the code for that use case fixes my search.
CREATE PROCEDURE dbo.Search
@keywords NVARCHAR(4000), -- e.g. '"building*" AND "consent*" AND "form*"'
@searchPhrase NVARCHAR(4000) -- e.g. 'building consent form'
-- Tmp table --
DECLARE @searchResults TABLE
ExactMatchToQuestionText bit default 0
-- Question contains keywords in order --
INSERT @searchResults (QuestionID, ExactMatchToQuestionText)
SELECT QuestionID, 1
WHERE QuestionText LIKE '%' + @searchPhrase + '%'
-- Free Text Search matches --
INSERT @searchResults (QuestionID, Rank, ExactMatchToQuestionText)
SELECT [Key], [Rank], 0
FROM CONTAINSTABLE(dbo.Question, QuestionText, @keywords, 100)
AND [Key] not in (SELECT QuestionID from @searchResults)
-- Return --
FROM @searchResults [Results]
JOIN Question ON Results.QuestionID = Question.QuestionID
This code still has a bug – a search for [what are building consents] returns 1 hit, but a search for [what building consents] returns 0, because “what” is a SQL Server stopword. D’oh!
At this point I am better off throwing away SQL Full Text Search and completely rolling my own query.
So why doesn’t SQL Server Full Text Search include a simple option for doing AND searches against the FREETEXTSEARCH table?
And, does anyone know a better way to do AND searches using SQL Server Full Text Search?