Show\Hide results pane in SQL server management studio

To change keyboard shortcut for show/hide results pane, go to Tools Options in SQL Server Management Studio:

Navigate to Environment > Keyboard > Keyboard.
Search for “Window.ShowResultsPane” using “Show commands containing” textbox.
In the “Press shortcut keys” textbox, press Cntrl + R buttons on the keyboard at the same time.
Click the “Assign” button and then click “OK“.

You might need to restart SQL Server Management Studio to apply these changes.

Continue Reading

Configure website on 000WebHost hosting provider and Google Domains.

Google Domains Configuration:

1: Login into Google Domains account.
Go to “Configure DNS” tab and add ns01.000webhost.com, ns02.000webhost.com as “custom name servers” as shown below.
Also add a “custom resource record” as below. The “Data” column should be the url of your website on 000WebHostApp.com

000WebHost Configuration:

Login into your 000WebHost account. In your website 000webhost panel, go to Set web address -> Add domain.
Enter your domain name and click Park domain

If your nameservers are pointed correctly to 000webhost nameservers, domain will show status Ready which means that is ready to link Website.
Click Manage -> Link website.

Website Admin configuration:
Login into your website \wp-admin. Go to Settings=>General. Update the “WordPress Address URL” and “Site Address URL” to your website’s url.

Continue Reading

Filter network requests when debugging.

When you are debugging, you would like to filter out the various requests so you can concentrate on what’s important eg: filter image or css file requests. Google has finally listened to the developer community and has added support for “negative filters” in Google Chrome-version 42.
Here is the text filter I use when debugging where the “-” sign before each file extension keyword filters the requests. The requests then do not show in the “Network” tab:

-png -svg -aspx -axd -jpg -css -ttf -js -gif -ico -woff2

When you are adding the text filter make sure that the “Regex” checkbox is unchecked.

You can further filter the requests by using the following supported keywords

eg:

-mime-type:image/png,-larger-than:20k,-domain:yoursite.com,-status-code:404
Continue Reading

Configure fiddler to not capture requests for certain domains

When you are debugging using fiddler, fiddler generally captures requests from all the domains. An easy way to not capture requests from certain domains is to use the build in “filters” tab in fiddler. Use the “Hide the following hosts” dropdown and key in the list of all the domains that you want to hide.

If you want more granular control then you can override the OnBeforeRequest method in Fiddler’s script using Fiddler’s Script Editor.

static function OnBeforeRequest(oSession: Session) {        
        if (
        	oSession.HostnameIs("www.google.com") || 
        	oSession.HostnameIs("inbox.google.com") || 
        	oSession.HostnameIs("13.client-channel.google.com") || 
        	oSession.HostnameIs("d.docs.live.net") || 
        	oSession.HostnameIs("slack.com") || 
        	oSession.HostnameIs("notifications.google.com") || 
        	oSession.HostnameIs("play.google.com") || 
        	oSession.HostnameIs("cello.client-channel.google.com") || 
        	oSession.HostnameIs("lp-push-server-777.lastpass.com") || 
        	oSession.HostnameIs("ssl.gstatic.com") || 
        	oSession.HostnameIs("clients4.google.com") || 
        	oSession.HostnameIs("clients1.google.com")
        )
        {  
            oSession["ui-hide"] = "hidden";    
        }
        //code omitted for brevity
}
Continue Reading

Access Team Foundation Server Source Control from Windows Explorer

I lot of times when I search for a file in Agent Ransack and I want to check who made the last change in TFS source control, I have to switch to Visual studio and check the history for that file in TFS source control. An easier way to do this is to install Microsoft Visual Studio Team Foundation Server Power tools. Microsoft Visual Studio Team Foundation Server Power tools provides integration between windows explorer and TFS source control. Please see the screenshots below.

 

 

 

 

Continue Reading

Fiddler not capturing traffic from browsers

Sometimes when you are trying to capture browser traffic for debugging, Fiddler does not capture the same.
One of the options to try is to make sure that the “Use Filters” checkbox is unchecked in the “Filters” tab in Fiddler.

You can also troubleshoot filters to see which filters are hiding your traffic.

If this does not work then you might have an extension (Like ZenMate or TunnelBear) installed that might have messed with your traffic. You might need to disable those extensions to ensure that Fiddler captures all the traffic from the browser.

Continue Reading

Improve performance of T-sql statements containing encrypted columns.

We can use database level encryption to encrypt sensitive information. A commonly used level of encryption is to only encrypt columns that contain sensitive information like credit card numbers, date of birth etc. Encrypting and decrypting database level information is very CPU resource intensive. If a column is used as a primary key or used in comparison clauses (WHERE clauses, JOIN conditions) the database will have to decrypt the whole column to perform operations involving those columns.

We faced a similar performance issue in one of the previous companies. Database selects based on SSN were very slow as the database had to decrypt the whole column to perform comparison. If you look at the information below(under Current Problem) the database performed 4413 logical reads, 4406 read-ahead reads and took 302 milli seconds to perform the operation.

To improve the performance of these kinds of queries, we decided to store the last four digits of the SSN in clear text and index the column as a non-clustered index. First filtering the rows based on last four digits of the SSN and then filtering the rows based on SSN lead to a dramatic improvement in performance. f you look at the information below(under Improved Solution) the database performed 27 logical reads, 10 physical reads 10, 8 read-ahead reads and took 71 milli seconds to perform the operation.
All in all a huge performance improvement.

Current Problem:

begin
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
SET STATISTICS TIME ON;
SET STATISTICS IO ON;

declare @ssn varchar(max) = '123123123'
with PersonalIdentifierInformationCTE as
(
select CONVERT (CHAR(32), DECRYPTBYKEYAUTOCERT(CERT_ID('CertificateName'), NULL, Identifier)) as Identifier
from PersonalIdentifierInformation
)
select *
from PersonalIdentifierInformationCTE where Identifier is not null
and Identifier=@ssn
end
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
(8 row(s) affected)
Table 'PersonalIdentifierInformation'. Scan count 1, logical reads 4413, 
physical reads 3, read-ahead reads 4406, lob logical reads 0, 
lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 250 ms, elapsed time = 302 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

Improved Solution:

begin
declare @ssn varchar(max) = '123123123'

DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
SET STATISTICS TIME ON;
SET STATISTICS IO ON;

with PersonalIdentifierInformationCTE as 
(
select CONVERT (CHAR(32), DECRYPTBYKEYAUTOCERT(CERT_ID('CertificateName'), NULL, Identifier)) as Identifier, SSNLast4
from PersonalIdentifierInformation
)
select *
from PersonalIdentifierInformationCTE 
where SSNLast4<> 0 and SSNLast4 = substring(@ssn, LEN(@ssn)-4+1, LEN(@ssn)) 
and Identifier is not null
and  Identifier=@ssn
end
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 13 ms.
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 2 ms.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
 SQL Server Execution Times:
   CPU time = 16 ms,  elapsed time = 16 ms.
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
    (8 row(s) affected)
Table 'PersonalIdentifierInformation'. Scan count 1, logical reads 27, 
physical reads 10, read-ahead reads 8, lob logical reads 0, 
lob physical reads 0, lob read-ahead reads 0.
    (1 row(s) affected)
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 71 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

Continue Reading

Only step into our files when debugging in Chrome Developer tools.

A lot of times when we are debugging our front end javascript code using Chrome Developer tools,

the system steps into other files. To avoid this, we can ask the debugger to ignore these files by “blackboxing” them. To do this, right click on the file and click “blackbox script”

To get to the blackboxed files, go to the “settings” section by clicking on the symbol as shown below or press F1.

The “blackboxing” tab shows all the files that have been blackboxed.

Continue Reading