The DBA Blog by Tomer Lev

SQL information, scripts, tricks and more…

SSIS Logging and Performance

SSIS Logging and monitoring

  • SQL Server Integration Services includes log providers that you can use to implement logging in packages, containers, and tasks. With logging, you can capture run-time information about a package, helping you audit and troubleshoot a package every time it is run. For example, a log can capture the name of the operator who ran the package and the time the package began and finished.

When you add the log to a package, you choose the log provider and the location of the log. The log provider specifies the format for the log data: for example, a SQL Server database or text file.

MSDN link explanation you can find here:

http://msdn.microsoft.com/en-us/library/ms140246.aspx

  • Here you can find how to enable logging in the package (I have also explained Daniel how to do it):

http://msdn.microsoft.com/en-us/library/ms141212.aspx

  • Integration Services includes logging features that write log entries when run-time events occur and can also write custom messages.

Integration Services supports a diverse set of log providers, and gives you the ability to create custom log providers. The Integration Services log providers can write log entries to text files, SQL Server Profiler, SQL Server, Windows Event Log, or XML files.

Here you can find how to configure your SSIS package:

http://msdn.microsoft.com/en-us/library/ms138020.aspx

  • SQL Server Integration Services provide a set of performance counters. Among them the following few are helpful when you tune or debug your package:
  • Buffers in use
  • Flat buffers in use
  • Private buffers in use
  • Buffers spooled
  • Rows read
  • Rows written

“Buffers in use”, “Flat buffers in use” and “Private buffers in use” are useful to discover leaks. During package execution time, you will see these counters fluctuating. But once the package finishes execution, their values should return to the same value as what they were before the execution. Otherwise, buffers are leaked. In occasions like that, please contact Microsoft PSS.

“Buffers spooled” has an initial value of 0. When it goes above 0, it indicates that the engine has started memory swapping.

“Rows read” and “Rows written” show how many rows the entire Data Flow has processed. They give you an overall idea about the execution progress.

  • Here is a link for top 10 SSIS best practices:

http://sqlcat.com/top10lists/archive/2008/10/01/top-10-sql-server-integration-services-best-practices.aspx

  • Here is some code you can use in order to store the logging to Audit table:

CREATE TABLE AuditPackage (Id INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
PackageName VARCHAR(100) NOT NULL,
PackageGuid UNIQUEIDENTIFIER,
ExecutionGuid UNIQUEIDENTIFIER,
StartTime DATETIME,
EndTime DATETIME,
ElapsedTime INT,
Status VARCHAR(100));
GO

CREATE PROCEDURE dbo.sp_dts_addlogentry @event sysname,
@computer nvarchar(128),
@operator nvarchar(128),
@source nvarchar(1024),
@sourceid uniqueidentifier,
@executionid uniqueidentifier,
@starttime datetime,
@endtime datetime,
@datacode int,
@databytes image,
@message nvarchar(2048)
AS
BEGIN
INSERT INTO sysdtslog90 (event, computer, operator, source, sourceid, executionid, starttime, endtime, datacode, databytes, message)
VALUES (@event, @computer, @operator, @source, @sourceid, @executionid, @starttime, @endtime, @datacode, @databytes, @message);

INSERT INTO AuditPackage (PackageName, PackageGuid, ExecutionGuid, StartTime, ElapsedTime)
SELECT @source, @sourceid, @executionid, GETDATE(), 0
WHERE (@event = ‘PackageStart’);

UPDATE AuditPackage
SET EndTime = GETDATE(),
ElapsedTime = DATEDIFF(ms, StartTime, GETDATE()),
Status = ‘Complete’
WHERE (@event = ‘PackageEnd’
AND PackageGuid = @sourceid
AND ExecutionGuid = @executionid);

UPDATE AuditPackage
SET Status = ‘Error’
WHERE (@event = ‘OnError’
AND PackageGuid = @sourceid
AND ExecutionGuid = @executionid);
END

The SSIS logging gives you the ability to find bottlenecks in your SSIS package like:

  • How much time it takes to validate packages
  • Trace each step and find out how long it takes
  • Gives you a tool you can use to log valuable events of the package.

SSIS: Custom Logging Using Event Handlers

SQL Server Integration Services (SSIS) contains some really useful logging procedures but as with most things in SSIS, it is extensible. There are 2 methods of extending the logging capability of SSIS:

  • Build a custom log provider
  • Use event handlers

Look at this link in order to find out which events to capture and how to customize them:

http://blogs.conchango.com/jamiethomson/archive/2005/06/11/SSIS_3A00_-Custom-Logging-Using-Event-Handlers.aspx

Advertisements

July 8, 2009 Posted by | Administration, General, SQL SERVER, Tips | , , , , | 1 Comment

Kill active database connections

— Create the sql to kill the active database connections
declare @execSql varchar(1000), @databaseName varchar(100)
— Set the database name for which to kill the connections
set @databaseName = ‘your db name’

set @execSql = ”
select  @execSql = @execSql + ‘kill ‘ + convert(char(10), spid) + ‘ ‘
from    master.dbo.sysprocesses
where   db_name(dbid) = @databaseName
and
DBID <> 0
and
spid <> @@spid
exec(@execSql

or the easy way is:

-alter database dbName set single_user with rollback immediate

and after you want to bring it back to normal status

-alter database dbName set multi_user with rollback immediate

July 6, 2009 Posted by | Scripts, SQL SERVER | , | Leave a comment

DBA Production Tasks

Here are some of production DBA tasks, I could think of:

Schema changes
Version Upload
Staging
Chrisis
SQL JOBS
ETL data transfer
Index maintenance
Backups
Permissions
monitoring
shrinks
server check
Archive data
Optimize queries
Programmers tasks
Reporting Services
Automate notifiations
High Availability
Scaling Up
OLAP cube – in case there is a need….
SSIS packages
Creating logical and physical objects
Establishing multiple DBEnvironment connections
Maintaining buffers for data and log pages
Managing transactions and locks

I’m sure there are more….

July 5, 2009 Posted by | General, SQL SERVER | | Leave a comment

Good DBA must support his clients

It does not really important what kind of a DBA are you: applicative or infrastructure one… the one thing you will always need to do is giving support and help to your clients: a client can be a market sales man, analyst, programmer or your CTO. You need high skills of support, if you want to be a professional DBA (and more…, but this one is very important also). Your sales man will come and ask you to give him all reports on sales made yesterday, and on which hour and to slice in by community; the analyst will want a report on member’s usage and the programmer will need help in T_SQL. You must know how to help all your clients, even if they come all at once, you will need to know how to say politely to each one of them when you will be available for doing the requested work. If you will answer politely with no press – they will all say it is nice to work with you and that you look professional in your line of duty. You, from the other hand, will need to do all you have promised to your clients. A good DBA, even if he is very professional, it does not look good, if he does not know how to give service to his clients. For example, if a programmer comes to you and wants your help in writing a stored procedure, and you are very busy with other things, give him a service by telling him that you have 10 minutes for him now, and you will be happy to help him later. I saw the other example, that a programmer came to a DBA to ask him for this help, and the DBA told him he does not have the time for him and he does not do these things. The first thing this programmer did is telling his colleagues not to go to the DBA and ask for help – not just this made the DBA a “bad name”, but also the company he works in damaged, which can lead also to bad performance on servers, due to the fact the programmer will upload a stored procedure to production servers with no help from the DBA, because the DBA does not want to help – it’s a pity! And wrong!

Want to be a good DBA – you must know how to give service to your clients!

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

Defragmenting Indexes in SQL Server 2005

http://weblogs.sqlteam.com/tarad/archive/2008/09/03/Defragmenting-Indexes-in-SQL-Server-2005.aspx

I think this may come in handy…

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

Tweet-SQL

Integrate your application in super quick time using Tweet-SQL. Tweet-SQL promises to allow fast and easy Twitter Integration through the use of CLR stored procedures on Microsoft SQL Server. A quick summary of Tweet-SQL…

http://sqlclrnews.blogspot.com/2008/09/easy-twitter-integration-with-tweet-sql_3653.html

June 26, 2009 Posted by | General, Scripts, 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