The DBA Blog by Tomer Lev

SQL information, scripts, tricks and more…

Sql server 2008 tools

find it here:

http://www.microsoft.com/downloads/details.aspx?FamilyId=228DE03F-3B5A-428A-923F-58A033D316E1&displaylang=en

Advertisements

June 26, 2009 Posted by | General, SQL SERVER | | Leave a comment

Craig Freedman’s SQL Server Blog

http://blogs.msdn.com/craigfr/

It seems like a very interesting blog. You might find it useful…

June 26, 2009 Posted by | General, Tips | , , | Leave a comment

Data Warehouse Query Performance

sql server 2008
Data Warehouse Query Performance:

http://technet.microsoft.com/en-us/magazine/2008.04.dwperformance.aspx

June 26, 2009 Posted by | Administration, General, SQL SERVER, Tips | , | Leave a comment

Internals Viewer for SQL Server

http://www.codeplex.com/InternalsViewer

Useful for extreme optimizations 🙂

June 26, 2009 Posted by | General, Scripts | Leave a comment

SQL Server 2005 SP3

check it out over here:

http://sqlblog.com/blogs/aaron_bertrand/archive/2009/01/18/my-latest-sp3-experiences-two-thumbs-acting-like-divining-rods.aspx

June 26, 2009 Posted by | General | Leave a comment

Microsoft SQL Server 2005 Integration Services Step by Step (Free E-Book)

check it out over here:

http://csna01.libredigital.com/?urrs4gt63d

enjoy 🙂

June 26, 2009 Posted by | SQL SERVER | Leave a comment

Clean Cache

Lets say you have a query and you need to check performance for this one.
Somtimes you will need to clean the cache – it is quite rare, but still there…
In order to clean the cache from any data, use the following:
dbcc dropcleanbuffers;
to clean any execution plan use:
dbcc freeproccache;
you need to take under considuration that these kind of operations influence on the all system. The SQL will need to read disk pages phisically and recompile all exeution plans – so be carefull when doing it on production servers…

June 26, 2009 Posted by | Administration, SQL SERVER, Tips | , | Leave a comment

Full Text Search Rank

The example is like the following:

I have in AlgoData table the following xmls containning the following strings inside of them:

Dogs: My dog is really terrified of everything., pack leader, loud …
I love my dog
How can I take care of my dog’s seperation anxiety?
Should I fire my dog walker? | Ask Metafilter
My dog is afraid of the outdoors – Pets Forum – GardenWeb
my dog
Raise your kid like a dog (?!)
My dog is dog aggressive What Can I do about this aggression
Creampuff Revolution: Creampuff to Dog: You Smell Like Gym Socks
would like to take my dog from the uk to tenerife by ferry …
Are Friendly Dogs Welcomed? – Santa Fe Scoop
tBlog – I Had My Dog Euthanized Yesterday
Dogs: My dog is really terrified of everything., pack leader, loud …
FIGHTING DOGS – SPEAK OUT AGAINST THE BREED BAN IN GERMANY!
Selection Quiz for Choosing a Dog
Pets on my Mind – Commercial Appeal Blogs
Baby Steps: More Belly-Achin’ and Dog Love
Hoover SteamVac Carpet/Upholstery Detergent – washes that dog …
Dog Illness And Treatments (Pets: Dog Health)

I needed to develop a script that will calculate the ranking like the follows:

DECLARE @topRank int

set @topRank=(SELECT MAX(RANK) FROM
freetexttable( AlgoData, AlgoData,‘I would like to take my dog out’,1))

SELECT
ftt.RANK,
(CAST(ftt.RANK as DECIMAL)/@topRank) as matchpercent,
AlgoData.AlgoDataID,
AlgoData.xmlfilename
FROM  AlgoData
INNER JOIN
FREETEXTTABLE( AlgoData, AlgoData, ‘I would like to take my dog out’) as ftt
ON
ftt.[KEY]= AlgoData.AlgoDataID
ORDER BY ftt.RANK DESC

Small explanation:

The table these functions return contains two columns:

  1. KEY: this contains the primary key of the row in the indexed table. Its data type is the same as the primary key in that table.
  2. RANK: this is a positive integer representing the relative strength of the match to your search term. This number is not an absolute value—it does not start at some fixed value and work down to near zero. The specific value is only relative compared to other results from that Free-Text query. Comparing the RANK value across different Full-Text queries does not necessarily get meaningful results.

Now, I have made just INNER JOIN the table returned from the Free-Text function on the KEY field to our desired results, then sort by the RANK column for sorting effects.

For a match percentage, I needed to divide the RANK by the top RANK value. It cannot be done unfortunately, with just one Free-Text query; it requires selecting the TOP value then selecting the desired results and creating a calculated field for the percentages.

Percentage matches (As sometimes you can see on some site search engines):

f

Now, I will go back and remind you I have looked for: ‘I would like to take my dog out’

And I have inserted the following xml in db already:

Dogs: My dog is really terrified of everything., pack leader, loud …
I love my dog
How can I take care of my dog’s seperation anxiety?
Should I fire my dog walker? | Ask Metafilter
My dog is afraid of the outdoors – Pets Forum – GardenWeb
my dog
Raise your kid like a dog (?!)
My dog is dog aggressive What Can I do about this aggression
Creampuff Revolution: Creampuff to Dog: You Smell Like Gym Socks
would like to take my dog from the uk to tenerife by ferry …
Are Friendly Dogs Welcomed? – Santa Fe Scoop
tBlog – I Had My Dog Euthanized Yesterday
Dogs: My dog is really terrified of everything. Pack leader, loud …
FIGHTING DOGS – SPEAK OUT AGAINST THE BREED BAN IN GERMANY!
Selection Quiz for Choosing a Dog
Pets on my Mind – Commercial Appeal Blogs
Baby Steps: More Belly-Achin’ and Dog Love
Hoover SteamVac Carpet/Upholstery Detergent – washes that dog …
Dog Illness And Treatments (Pets: Dog Health)

As you can see above, the query returned RANK results under the RANK column above and I have made calculation of the match percent under the matchpercent column above.  In fact these are the strings that returned high rank with in this search query: “I love to take my dog out”

  1. “My dog is dog aggressive What Can I do about this aggression”
  2. “Dogs: My dog is really terrified of everything., pack leader, loud …”
  3. “Dog Illness And Treatments (Pets: Dog Health)”

As you can see above, the full-text search engine returned RANK 178 with percent of 0.79 to the first 3 and the others got RANK 126 with percent of 0.56.

All the rest returned lower percent value as you can see.

** The RANK calculation is not something I can control, and calculation is done via the link I gave you above.

Conclusion:

It depends what is the occurrence you want for your results. If you don’t need them to be that accurate may be you can consider using it.

June 23, 2009 Posted by | full text search | , | Leave a comment

Full Text Search Analysis

Full-Text Search Overview

Full-text queries perform linguistic searches against text data in full-text indexes by operating on words and phrases based on rules of a particular language such as English or Japanese. Full-text queries can include simple words and phrases or multiple forms of a word or phrase.

Full-text search is applicable in a wide range of business scenarios such as e-businesses—searching for items on a web site; law firms—searching for case histories in a legal-data repository; or human resources departments—matching job descriptions with stored resumes. The basic administrative and development tasks of full-text search are equivalent regardless of business scenarios. However, in a given business scenario, full-text index and queries can be honed to meet business goals. For example, for an e-business maximizing performance might be more important than ranking of results, recall accuracy (how many of the existing matches are actually returned by a full-text query), or supporting multiple languages. For a law firm, returning every possible hit (total recall of information) might be the most important consideration.

Support Forms of Query Terms (Full-Text Search)

http://technet.microsoft.com/en-us/library/cc879300.aspx – this topic introduces the forms of full-text queries supported by SQL Server, and summarizes the support provided for each form of query by the full-text predicates and rowset-valued functions. Full-text queries can be run only on columns that have been full-text indexed.


Full-Text Search Tests

The following topic will describe the tests have been made on full-text search. The tests have been made to English and Hebrew languages.

The following tests have been made:

  1. Inflectional forms of a specific word (generation term) – The inflectional forms are the different tenses of a verb or the singular and plural forms of a noun. For example, search for the inflectional form of the word “drive”. If various rows in the table include the words “drive”, “drives”, “drove”, “driving”, and “driven”, all would be in the result set because each of these can be inflectionally generated from the word drive.
  1. Synonymous forms of a specific word (thesaurus) – A thesaurus defines user-specified synonyms for terms. For example, if an entry, “{car, automobile, truck, van}”, is added to a thesaurus, you can search for the thesaurus form of the word “car”. All rows in the table queried that include the words “automobile”, “truck”, “van”, or “car”, appear in the result set because each of these words belong to the synonym expansion set containing the word “car”.
  1. A word or a phrase where the words begin with specified text (prefix term) – A prefix term refers to a string that is affixed to the front of a word to produce a derivative word or an inflected form.

For a single prefix term, any word starting with the specified term will be part of the result set. For example, the term “auto*” matches “automatic”, “automobile”, and so forth.

For a phrase, each word within the phrase is considered to be a prefix term. For example, the term “auto tran*” matches “automatic transmission” and “automobile transducer”, but it does not match “automatic motor transmission”.

  1. A word or phrase close to another word or phrase (proximity term) – A proximity term indicates words or phrases that are in close proximity to another word or phrase or two words or phrases in any order.

For example, you want to find the rows in which the word “ice” is near the word “hockey” or in which the phrase “ice skating” is near the phrase “ice hockey”. Whether two terms or phrases are considered to be near to each other is calculated internally and is not configurable by the user. Many data points are considered when calculating nearness.

Each of the above will be detailed in the next topics; dividing tests have been made for each one.

Test Number #1: Inflectional forms of a specific word

The test has been made for checking matching between words like Cow-Cows.

The test has been made like the following:

4.1.   An row had been inserted inside AlgoData table using the following procedure:

alter PROCEDURE [dbo].[LoadAlgoData]

@xmlFileName nvarchar(300),

@AlgoDataid bigint,

@Algotype int,

@createuser nvarchar(50)= NULL,

@updateuser nvarchar(50) = null,

@AlgoDataExtension varchar(10)

AS

BEGIN

declare @strsql varchar(8000)

OPENROWSET

set @strsql = ‘INSERT INTO AlgoData(AlgoDataID,AlgoType, createdate, AlgoDataExtension,XmlFilename,AlgoData) ‘

set @strsql = @strsql + ‘SELECT ‘ + cast(@AlgoDataid as varchar) + ‘,’ + cast(@Algotype as varchar) + ‘,getdate(),’ +  ”” + @AlgoDataExtension + ”” + ‘,’ + ”” + @xmlFileName + ”’, xmlData

FROM

(

SELECT  *

FROM    OPENROWSET (BULK ”’ + @xmlFileName + ”’ , SINGLE_BLOB) AS XMLDATA

) AS FileImport (XMLDATA)

print @strsql

exec(@strsql)

END

5.2   The following execution has been invoked:

exec [dbo].[LoadAlgoData] ‘C:\Users\tomer\Downloads\Backup\XMLAQuery3\XMLAQuery11.xml’,11,1,’tomer’,’tomer’,’XML’

  • First test – The XML contined the words ‘cow’ and ‘cows’ in it.
  • Second test – The XML contined the words תפוח and תפוחים in it.
  • The same test had been done on: ‘go’,’went’,’going’ words.

5.3  The next query has been invoked:

SELECT *

FROM AlgoData

WHERE CONTAINS (Algodata, ‘FORMSOF(INFLECTIONAL, “cow”)’);

Result: A record set has been returned, finding the match.

Test Number #2: Synonymous forms of a specific word

Checking verbs match has been done on the following House, Home, and apartment.

This is done using a thesaurus defines user-specified synonyms for terms.

1.1.   An row had been inserted inside AlgoData table using the above  procedure

1.2.   The following execution has been invoked:

exec [dbo].[LoadAlgoData] ‘C:\Users\tomer\Downloads\Backup\XMLAQuery3\XMLAQuery11.xml’,11,1,’tomer’,’tomer’,’XML’

  • First test – The XML contined the words ‘House’ where as the search has been made for ‘Home’.
  • Second test – The XML contined the words דירה in it, where as the search has been made for בית.
  • The dictionary of synonims words can be enlarged and needs to be configured in 1.3 below.

1.3.   A thesaurus file has been edited to determine these verbs as the following:

<XML>

<thesaurus xmlns=”x-schema:tsSchema.xml”>

<diacritics_sensitive>0</diacritics_sensitive>

<expansion>

<sub>Home</sub>

<sub>House</sub>

<sub>Appartment</sub>

</expansion>

</thesaurus>

</XML>

1.4    The next query has been invoked:

SELECT *

FROM AlgoData

WHERE CONTAINS (AlgoData, ‘FORMSOF(THESAURUS , house)’);

Go

SELECT *

FROM AlgoData

WHERE CONTAINS (AlgoData, ‘FORMSOF(THESAURUS , “דירה”)’);

Result: A record set has been returned, finding the match.

Test Number #3: A word or a phrase where the words begin with specified text

The test done in this topic was for the words:

I love to take my dog out, I love my dogs

1.1.   An row had been inserted inside AlgoData table using the above  procedure

1.2.   The following execution has been invoked:

exec [dbo].[LoadAlgoData] ‘C:\Users\tomer\Downloads\Backup\XMLAQuery3\XMLAQuery11.xml’,11,1,’tomer’,’tomer’,’XML’

  • The XML contined the phrases ‘I love to take my dog out’ and ‘I love my dogs’.
     1.3 

SELECT *
FROM AlgoData
WHERE CONTAINS (Description, ' "I love*" ' );

Result: A record set has been returned, finding the match.


Test Number #4: A word or phrase close to another word or phrase (proximity term)

The test has been made here search for words or phrases in close proximity to another word or phrase.

For example:

SELECT AlgoDataID, AlgoData
FROM AlgoData AS L1 INNER JOIN
CONTAINSTABLE(AlgoData, AlgoData, '(love ~ my ~ dog)' ) AS KEY_TBL
ON L1.AlgoDataID = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK;
  • The XML contined the phrases ‘I love to take my dog out’ and ‘I love my dogs’.

This example searches for the word “love” close to the word “my” close to word “dog”.

** Need to mention that all of the tests have been done also for Hebrew and also for English.

June 23, 2009 Posted by | full text search | , | Leave a comment

50 top high CPU queries

select top 50

st.objectid, st.dbid,

— object_name(st.objectid, st.dbid), — uncomment this line if you’re using SQL 2005 SP2

total_worker_time/execution_count AS AverageCPUTime,

CASE statement_end_offset

WHEN -1 THEN st.text

ELSE SUBSTRING(st.text,statement_start_offset/2,statement_end_offset/2)

END AS StatementText

from

sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st

ORDER BY AverageCPUTime DESC

June 22, 2009 Posted by | Scripts, SQL SERVER | , , , , , , , | Leave a comment