Saturday, July 19, 2014

Duration column in SQL Profiler/ SQL Traces

We all familiar to the 'Duration' field in the profiler,
But not all of us aware that 'Duration' includes the waits + CPU time,
Meaning it also includes the time it takes to transfer the result set
to an application over the network. However the CPU time, it's the amount
of CPU time it took to complete the query.

Let`s make it visual, suppose we have SQL Server and in one case an application
in same network and in other case the application installed in another network.

And our application executes same SP. If the network B has slow connection to
Our SQL Server we will see that the duration in the SQL Profiler in case of Network A
Will be different that the duration in Network B, but the CPU time will be similar in
Both cases.
Also pay attention to the duration units.
In Profiler, the duration is represented as milliseconds (1 sec = 1000 ms),
But I saved trace file in the duration is represented
As microseconds (1 sec = 1000000 μs).

Saturday, January 18, 2014

TFS 2012 Tips - Part 1

 1) Copy work item name with ID

Often we need to copy the work item name and ID,
For example, to copy the ID and name of a bug, requirement or task.
This can be done by opening a work item and double clicking
on the title and it will copy to the clipboard its name and the ID,
all you need is to do 'paste'. Try it.
Other method is to click on the "O" icon and it will open new email
with this item in the outlook and on the email subject you can see the
work item ID and name and you can copy it from there.

2)  Ctrl + C will copy the full URL of the document in the TFS
 when you stand on it.

When you are in “Documents” section and you
Select a file and you want to copy its URL you can press Ctrl+C
Or right mouse click and press on “Copy” option
Open notepad and press Ctrl+V and you will get the
Full URL to the file.



3) Compare files on double click in Pending Changes view

In “Pending Changes” screen in the Team Explorer
When you double click to the file on default behavior
the file will be opened in the code editor.

Press Shift + double click to open Compare tool to compare
the differences between the file and the workspace version

4) DB toolbar disappears when solution is opened.

If you have DB project in the Source Control, you
can open a stored procedure in the VS, connect to a DB
and work against a DB. To connect the a DB we have
'connect' option in the toolbar.
But the issue is that this toolbar disappears when we have
solution opened in the Solution Explorer. Close the solution
and the toolbar will appear again.

Friday, December 6, 2013

How to get Page ID for row(s) in a table in SQL Server

There can be situations like in my previous post
Attempt to fetch logical page # in database failed
when you need to know to which page belong row(s)
in a table.

Here a query that can help you:

SELECT TOP 100 plc.*, c.*
FROM  dbo.Customers as c (nolock)
      CROSS APPLY sys.fn_physLocCracker (%%physloc%%) As plc

It gives you the database file id, page id within the file,
and slot number on the page:

There is also "sys.fn_physlocFormatter" function that simply
formats the output of the "fn_physLocCracker" function.

SELECT TOP 100 plc.*,
                   sys.fn_physlocFormatter(%%physloc%%) as [file_id:page_id:slot_id],g.GameID
FROM  dbo.Games as g (nolock)
             CROSS APPLY sys.fn_physLocCracker (%%physloc%%) As plc
WHERE g.gameid=1

Keep in mind that the function "fn_physLocCracker"
is undocumented and exists only in SQL Server 2008 and above.

Monday, November 18, 2013

Attempt to fetch logical page # in database failed. It belongs to allocation unit ....

What do you do when you get errors like below?
Attempt to fetch logical page () in database failed.
It belongs to allocation unit X not to Y.
Well, in most cases this error indicates to data
corruption issues in the database.
In one day, some of our SELECT queries started to fail
with this error in SQL Server 2008 R2 Production database.

Well, the first thing was to execute the DBCC CHECKTABLE and later DBCC CHECKDB.
Surprisingly, the DBCC CHECKTABLE and DBCC CHECKDB did NOT return any errors!
Also executing DBCC PAGE with the problematic page number returned data without any
errors. Also we executed the CHECKDB on Mirroring database and it also did not detect any errors.

The fact that the DBCC CHECKDB did not detect any errors was encouraging
from one side and not clear from second side.
Before trying to repair the corrupted page by restoring the corrupted page from the backup,
it was decided to restart the SQL Service, because all the symptoms were leading to thoughts
that the corruption was in memory and the on-disk image of the page is not corrupt.
Fortunately, in our case it helped. After the restart, the SELECTs against the problematic rows
run successfully.You also may try to clean the DB`s cache instead of restarting the SQL

Bottom line, make sure you have a job that executes DBCC CHECKDB against your
production databases.

Thursday, August 8, 2013

Inserting rows using OUTPUT.. INTO.. a table with Identity column

Let`s suggest you have some simple archiving process that implemented as deleting rows
from the source table and inserting the deleted rows to the archive table that has
same schema as the source table.

For example:
Let`s create 2 tables with SAME schema (SourceTable and TargetTable)
and insert 3 rows to the "SourceTable":

CREATE TABLE dbo.SourceTable
  CustomerName VARCHAR(50) NOT NULL

TABLE dbo.TargetTable
CustomerName VARCHAR(50) NOT NULL

INSERT INTO dbo.SourceTable(CustomerName)
SELECT 'Customer1' as CustomerName
SELECT 'Customer2' as CustomerName
SELECT 'Customer3' as CustomerName

Pay attention that the "CustomerID" column defined as IDENTITY in both tables.
Now we will delete all rows from the "SourceTable" and by using the OUTPUT..INTO
insert them to the "TargetTable".

DELETE  FROM dbo.SourceTable
OUTPUT deleted.CustomerID,deleted.CustomerName
INTO dbo.TargetTable(CustomerID,CustomerName)

We will not get any errors, but we should.
We should get an error saying something like below:
"Cannot insert explicit value for identity column in table 'TargetTable' when IDENTITY_INSERT is set to OFF."

Why we should get the error?
Because we are trying to insert explicit values to column
that defined as Identity
and we did not set the IDENTITY_INSERT to ON in the TargetTable.
But in our case, we did not get this error!
And the rows were successfully inserted.
The question is why?

If you can explain it, please write it in the comments.

Friday, February 1, 2013

"Create Unit Tests" option greyed out in Database project

In the Visual Studio in database project, when you open Solution and switch to
Schema View , go to a stored procedure and right click, you should see the
option "Create Unit Tests...".
The problem is that it can be greyed out (sometimes).

"Create Unit Tests" option greyed out

In my case it is greyed out when there was no successful build of the database
project, try to issue build, fix errors if you have and see again the option
"Create Unit Test" is greyed out.

Saturday, November 3, 2012

Red Gate SQL Compare crashing after 20-30 minutes

Consider following situation:

I use Red Gate SQL Compare to compare database schema between
2 remote database servers. Usually it works fine and the process finishes
within couple of minutes.If you compare a DB where its objects encrypted,
for example, stored procedures, functions are encrypted, SQL Compare
has option to decrypt the encrypted objects and the comparing process
takes a little longer, but it works.

On some new installed Data Center the SQL Compare was getting the
following error after 20-30 minutes of comparing the local database and
the remote database on the new installed Data Center:

"A transport-level error has occurred when receiving results from the server.
(provider: TCP provider, error: 0 - An existing connection was forcibly closed
by the remote host.)"

Well, it took us some time to understand why on the new servers
we getting the "transport-level"/"time out" error above.
The bottom line: it happened due to "TCP Chimney" option that
was enabled in the Windows Server.
It means the following:
TCP Chimney Offload is a networking technology that helps
transfer the workload from the CPU to a network adapter
during network data transfer.
This should be a good thing, but in order to get it working correctly
and without any issues you need a NIC to support this feature
and proper drivers.Also it should be enabled in the OS and NIC
The error not happened in all rest data centers because this feature is
enabled/disabled by default in different Windows Servers.
For example, TCP Chimney is enabled by default in the
Windows Server 2003 SP2 and it disabled by default in the
Windows Server 2008.
To disable the TCP Chimney use the following:
netsh int tcp set global chimney=disabled
According to this article the TCP chimney can cause
also other problems related to SQL Server.
I hope this post will help you to save hours:)