Sunday, March 3, 2019

Slow Git operations in Visual Studio 2017

I have encountered a situation when every Git operation in the Visual Studio 2017 version 15.9.x in Windows 7 machine is slow.
For example: Checking out to a different branch, clicking on 'Changes', 'Sync', Fetch, Pull take time.
But, if you do these operations via command line,
all works fast.
In my case, clearing the AppData Visual Studio folder helped. Run %appdata%, delete or rename the 'Visual Studio' folder.

Thursday, January 3, 2019

Pre-Deployment scripts executed after dacpac compares and generates changes scripts

If you deploy your database with TFS,
You probably use Pre-Deployment and Post-Deployment scripts

The important thing to note is that the process is:

  1. sqlpackage compares the dacpac to the database and generates a list of changes to make
  2. Pre-Deployment scripts executed
  3. Auto generated script of changed executed (from point #1)
  4. Post-Deployment scripts executed

As you can see,
The Pre-Deployment scripts are not actually 
executed before the compare.

Let`s suppose you want to execute the Pre-Deployment
scripts before sqlpackage compares the dacpac 
to the database and generates a list of changes to make.

What you can do is to add a step that will execute the scripts
in the Pre-Deployment folder
Before executing the step of sqlpackage.
By this, the ‘compare’ will be
done after the Pre-Deployment scripts were executed.

Wednesday, May 2, 2018

Frequently running stored procedure hangs from time to time

We have encountered situation when we have a frequently
(about 50 times in a second) executed stored procedure

That hangs for couple of seconds and blocks all other
requests with this stored procedure.
We are talking about OLTP system.

In general, this SP runs for 20-30 ms,
but once in 2-3 days, we saw its runtime
increases up to 20-30 seconds
(only single thread/execution) and after that
all comes back to normal.
also when this ‘hanging’ occurs, all other threads that execute this SP wait for the problematic thread/execution to finish.

In the blocking event in the profiler, we saw that the other blocked
Threads that execute the same SP were blocked due to compile lock.

The issue was related to the fact that SQL Server
Before executing a SP, first checks if the Statistics are up to date,
And in case they are not, it will first update the statistics and only then
Continue to execute the SP by  re-compiling it and creating fresh execution plan.

The most interesting part is that all other threads with this SP were blocked
Until the process of updating statistics and creating the fresh execution plan finished.

The reason for the blocking of the rest concurrent calls to the same SP
Explained by below:

In Microsoft SQL Server, only one copy of a stored procedure plan is generally in cache at a time.
Enforcing this requires serialization of some parts of the compilation process,
and this synchronization is accomplished in part by using compile locks.
If many connections are concurrently running the same stored procedure and a compile lock must be obtained
for that stored procedure every time that it is run, system process IDs (SPIDs)
might begin to block one another as they each try to obtain an exclusive compile lock on the object.

We can control if we want the AUTO_UPDATE_STATISTICS to occur in synchronized way
or in A-Synchronized mode by setting the AUTO_UPDATE_STATISTICS_ASYNC option.

Once we set the AUTO_UPDATE_STATISTICS_ASYNC option to true, the problem was gone.

SELECT name, is_auto_update_stats_on, is_auto_update_stats_async_on
FROM   sys.databases

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.