11 Hidden Windows 10 Tips and Tweaks

Windows 10 - SeniorDBA

All the features of Windows 10 aren’t as obvious as they should be, and that includes the tips and tweaks that make the powerful operating system easier to use. In this article by Howard Wen, we learn the details of 11 lesser-known ways to make Windows 10 better.

1. Delete your previous Windows version installation

2. Know how to sign out of Windows 10 

3. Pick whatever accent color you want

4. Use the new delay timer in the Snipping Tool 

5. Change Edge’s default search engine from Bing to another one 

6. Delay automatic updates over Wi-Fi 

7. Record video clips using the Xbox app

8. Remove the OneDrive folder from File Explorer 

9. Pin Windows apps to the desktop

10. Access all Windows 10 settings under one user interface

11. Uninstall default Windows apps

 

You can read the entire article to see all the details.

SQL Server Management Studio Tricks

SQL Server

If you spend a lot of time in SQL Server Management Studio, you have probably managed to customize your interface to meet your basic requirements, but there might be some additional tips you just haven’t heard about.

 

Delete Whitespace

You can delete the whitespace around different lines of text in SSMS by going to Edit->Advanced->Delete Horizontal White Space, or by using the keyboard shortcut Ctrl + K, Ctrl + \.

Use Clipboard Ring

If you have ever copied some code to the clipboard only to copy something else over it before you get a chance to paste the original code, you need to know about the Clipboard Ring. Just press Ctrl + Shift + V and you can cycle through the last 20 items you copied to your SSMS clipboard.

Keyboard Shortcuts

There are a bunch of keyboard shortcuts that you can use within SSMS, and the default setting are based on Visual Studio.  You can see a list of keyboard shortcuts here. You can set some of these shortcuts through Tools -> Options -> Environment/Keyboard

image

Block Selection

Instead of selecting multiple rows, you might want to select multiple columns by holding down the Shift Key.  To do Block selection, you can do SHIFT+ALT and drag your mouse to only select certain areas of your text in column fashion.

image

Status Bar

The normal status bar is at the bottom of the query window.

image

Under Tools -> Options -> Text Editor/Editor Tab and Status Bar, there are options that allow you to change the appearance of the Status Bar.

image

By default, the Status Bar is set to Bottom for the location, but you can move it to the top if you choose.

image

You can change the color of the Status Bar as well.

Cycle through Query Windows

Almost everyone knows you can use ALT+TAB to cycle through programs in Windows.  You can use CTRL+TAB to cycle through Query Windows in SSMS.

image

Also, CTRL + F6 will cycle through the actual tabs without the graphic switching display.

Group Connections

Within Registered Servers you can create a group of SQL Servers.  This then allows you to start a query that will be run against all of the servers within the group.  This is where the Group Connection Color for the status bar comes into play.

image

This could be really handy if you need to execute items across multiple servers.  The color of the status bar is there to help you realize that that query is a group query as opposed to a single server connection.

You can get more information about shortcuts here.

 

Improve Stored Procedure Performance in SQL Server

sqlserver2014

These basic tips by Ahmad Osama help steer your database development to avoid common performance issues.

1. Use SET NOCOUNT ON

2. Use fully qualified procedure name

3. sp_executesql instead of Execute for dynamic queries

4. Using IF EXISTS AND SELECT

5. Avoid naming user stored procedure as sp_procedurename

6. Use set based queries wherever possible.

7. Keep transaction short and crisp

You can read the details for each tip at his article here.

Outlook Mistakes that May Disrupt Your Life

Microsoft-Outlook-2013

Just about everyone I know uses Microsoft Outlook for business email, and just about everyone I know uses about 5% of what Outlook can do to help with your email. There is an article by Eric Simson that lists some steps you can take to reduce the headaches caused by emails and when using Outlook.

  1. Regretting after Sending the Email
  2. Verifying the Recipient
  3. Set up MailTips Alerts for Common Mistakes

There is also some helpful tips here.

Recover Backed-up query files in SQL Server Management Studio

Recover backed-up query files in SQL Server Management Studio

This is a useful tip that was discovered awhile back, but may still be helpful today.

If SQL Server Management Studio (SSMS) ever crashes on you for whatever reason then there is a chance that you’ll lose whatever queries you had open at the time of the crash. Sometimes when you re-open SSMS it will ask you if you want to re-open any files that it can recover but for some reason it doesn’t always work correctly. That would mean any query you hadn’t saved is lost forever.

Even if SSMS does not prompt you to recover those files they are probably still present on your machine. Check in your C:\Users\[UserName]\Documents\SQL Server Management Studio\Backup Files\Solution1 folder and you should find them.

ssms-recover-files

You might have already figured out where they were stored, the real trick is knowing that they are stored in the first place.

Five SQL Server Performance Tuning Tips and Tricks

tips-tricks

1. Stop Shrinking Data Files

Shrinking data files has been a general bad practice for a long time, and it can really impact performance in one of three ways. The shrinking of data files can negatively impact performance because it can cause a lot of fragmentation, which means your subsequent queries might suffer. And if you don’t have Instant File Initialization turned on, the resultant growth later can also hinder performance and potentially cause timeouts. Although there are times when shrinking a file might be necessary, make sure that you know the impact before you try it.

2. Find Problem Queries

If you look at all the stored procedures or queries that run against an instance of SQL Server, you will find that it is just a few queries that are responsible for 80 percent of the poor performance that you see throughout the day. If you can identify these problem queries and prioritize tuning them, you can make a significant impact on the overall performance of your server.

One way that you can easily identify expensive statements is by using this code:

SELECT COALESCE(DB_NAME(t.[dbid]),'Unknown') AS [DB Name],
       ecp.objtype AS [Object Type],
       t.[text] AS [Adhoc Batch or Object Call],
       SUBSTRING(t.[text], (qs.[statement_start_offset]/2) + 1,
       ((CASE qs.[statement_end_offset]
              WHEN -1 THEN DATALENGTH(t.[text]) ELSE qs.[statement_end_offset] END
       - qs.[statement_start_offset])/2) + 1) AS [Executed Statement], 
       qs.[execution_count] AS [Counts], 
       qs.[total_worker_time] AS [Total Worker Time], 
       (qs.[total_worker_time] / qs.[execution_count]) AS [Avg Worker Time], 
       qs.[total_physical_reads] AS [Total Physical Reads],
       (qs.[total_physical_reads] / qs.[execution_count]) AS [Avg Physical Reads], 
       qs.[total_logical_writes] AS [Total Logical Writes],
       (qs.[total_logical_writes] / qs.[execution_count]) AS [Avg Logical Writes], 
       qs.[total_logical_reads] AS [Total Logical Reads],
       (qs.[total_logical_reads] / qs.[execution_count]) AS [Avg Logical Reads], 
       qs.[total_clr_time] AS [Total CLR Time], 
       (qs.[total_clr_time] / qs.[execution_count]) AS [Avg CLR Time], 
       qs.[total_elapsed_time] AS [Total Elapsed Time], 
       (qs.[total_elapsed_time] / qs.[execution_count]) AS [Avg Elapsed Time], 
       qs.[last_execution_time] AS [Last Exec Time], qs.[creation_time] AS [Creation Time]
FROM sys.dm_exec_query_stats AS qs
JOIN sys.dm_exec_cached_plans ecp ON qs.plan_handle = ecp.plan_handle
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t
-- ORDER BY [Total Worker Time] DESC
-- ORDER BY [Total Physical Reads] DESC
-- ORDER BY [Total Logical Writes] DESC
-- ORDER BY [Total Logical Reads] DESC
-- ORDER BY [Total CLR Time] DESC
-- ORDER BY [Total Elapsed Time] DESC
ORDER BY [Counts] DESC

The sys.dm_exec_query_stats DMV query contains aggregate performance statistics that are associated with each of the cached query plans in the SQL Server instance. This query easily enables you to order the results in several ways, depending on what type of resource usage you want to review. The key is to weigh the number of times that the statement was executed versus the average resource usage to better determine which statements would have the largest impact on performance if they were optimized better. Do not put too much weight on the total elapsed time or on the overall duration of the statement because other factors, such as blocking, can influence the overall duration. But by using this query, you should be able to quickly identify the top offending statements in your system, and then prioritize the statements so that you can tune them as efficiently as possible.

3. Locate I/O Bottlenecks

One of the reasons why performance suffers in SQL Server is I/O bottlenecks. You have three relatively easy methods at your disposal to determine whether you have I/O issues:

  • Check whether you see high page_IO_latch waits or log_write waits in your wait statistics.
  • Use the DMF sys.dm_io_virtual_file_stats() to locate any areas in which you have excessive physical I/O or excessive stalls on that I/O. These issues can occur at the database level or even at the file level.
  • Use the trusty PerfMon counters. At a minimum, use the Avg. Disk sec/Read and Avg. Disk sec/Write counters to see the latency of the reads and writes. On an OLTP system, you would, ideally, want to see log file latency to be just a few ms and data file latency to be less than 10ms. Remember that these are ideal values. Your system might tolerate larger latency and still be fine. Also keep in mind that many times when you find that the storage subsystem can’t keep up with the current demand, the cause might not be an I/O bottleneck at all. It might, instead, be pooled.

When you find that you have many physical I/O bottlenecks occurring, your first instinct should be to find the queries that are causing all the physical I/O, and then try to tune them before you add more hardware. One performance aspect that you should never ignore is high latency for log writes. If you start to hold up the writing to the log file, all further DML operations can quickly become impeded, and they’ll remain so until you alleviate the bottleneck. High latency in log writes is a sure way to hinder performance in the database.

4. Separate Data and Log Files

One of the most often disregarded rules for good performance is separating the data and the log files onto separate physical drive arrays whenever possible. The key principle here is to separate the mostly random access of the data files from the sequential access that occurs by writing to the transaction logs. One aspect familiar to a SAN environment is that even though you’re presented with different drive letters or LUNs, you can’t be sure that these represent different physical drive arrays. Often, these apparent drives are carved from the same larger disk array, and this will defeat the intent of separating them in the first place. So make sure that you know what you’re really getting when you ask for your storage on a SAN. You’ll be amazed at how much difference this can make as the volume of your transactions increases.

5. Monitor Index Usage

The sys.dm_db_index_operational_stats() DMF is a widely underutilized source of information. This object provides very useful stats on how many times an index has been used, locked, waited on, etc. An index in this context can mean several things: a clustered index, heap, index, or a partition of either of these. This object will give you feel for how indexes are being used, and just exactly how much a specific index is costing you. It does this in part by telling you how often the index is modified at the Leaf level or non-leaf level, as well as how often users waited on blocks associated with the object, which can mean SQL Server locks, or even hardware or I/O latches.

SELECT object_schema_name(ddios.object_id) + '.' + object_name(ddios.object_id) as objectName,
       indexes.name, 
       case when is_unique = 1 then 'UNIQUE ' else '' end + indexes.type_desc as index_type,
       page_latch_wait_count, 
       page_io_latch_wait_count
FROM sys.dm_db_index_operational_stats(db_id(),null,null,null) as ddios
JOIN sys.indexes on indexes.object_id = ddios.object_id
     and indexes.index_id = ddios.index_id
ORDER by page_latch_wait_count + page_io_latch_wait_count desc

You can read additional tips here.

10 Important Things for SQL Server

If you are a database administrator and you’re getting ready to design a database from scratch, what do you really need to include? What steps do you really need to take? Are there shortcuts that you can take or steps that you can skip?

tips-tricks

There are 10 essentials:

  1. Understand the database’s purpose.
  2. Get the right tool.
  3. Gather the requirements for the database.
  4. Be conscientious when modeling the data.
  5. Enforce relationships.
  6. Use the appropriate data types.
  7. Include indexes when modeling.
  8. Standardize the naming convention.
  9. Store the code that touches the data in SQL Server.
  10. Document the work.

Read the entire post by Michelle A. Poolet over at SQL Server Pro.

%d bloggers like this: