casinos

Posts Tagged ‘Performance’

SQLCMD Command line options September 18th, 2013

Vinod Kumar

Recently I was reading the blog from Pinal around Execute Operating System Commands in sqlcmd and was wondering do we know all the command line parameters available with SQLCMD? Personally for me this is a very handy utility and I have used it in the past like anything.

The sqlcmd utility lets you enter Transact-SQL statements, system procedures, and script files at the command prompt. This utility replaces the osql command-line tool with many scripting enhancements. Some of the common command-line options are:

Option

Description

-A

Logs in to SQL Server with a Dedicated Administrator Connection (DAC).

-d

Issues a USE  db_name statement when you start sqlcmd. This option sets the sqlcmd scripting variable SQLCMDDBNAME.

-E

Uses a trusted connection instead of using a user name and password to log on to SQL Server.

-i

Identifies the file that contains a batch of SQL statements or stored procedures. Multiple files may be specified that will be read and processed in order.

-N

This switch is used by the client to request an encrypted connection.

-o

Identifies the file that receives output from sqlcmd.

-P

Is a user-specified password. Passwords are case sensitive.

-q "cmd"

Executes a query when sqlcmd starts, but does not exit sqlcmd when the query has finished running. Multiple-semicolon-delimited queries can be executed.

-Q

Executes a query when sqlcmd starts and then immediately exits sqlcmd.

-t

Value will be used to give a query timeout value.

-U

Is the user login ID. If neither the -U option nor the -P option is specified, sqlcmd tries to connect by using Windows Authentication mode.

-Z

Changes Password and exits sqlcmd

This is not an exhaustive list of parameters that you can find but these are the most commonly used.

As I wrap up, wanted to call out one specific parameter which is –K with sqlcmd. When I was writing the book on AlwaysOn Availability Groups” (Paperback, Kindle), we wanted to show quickly how client redirection works with ApplicationIntent=ReadOnly and this handy parameter helps you achieve the same from sqlcmd.

Continue reading...


 

Find information about SQL Server 2012 AlwaysOn FCI September 4th, 2013

Vinod Kumar

When we were writing our AlwaysOn book, there were so much more to write that we couldn’t add a lot of content which we wished. Having said that, it still made to a 500+ pages which was something we didn’t realize. As a DBA, you need to administer several SQL Server 2012 instances at a given point in time. Before you can start your administration of SQL Server instances, I highly recommend we be clear on certain things:

  1. How many nodes are there in the system?
  2. Are these SQL Server instances Clustered currently?
  3. Which is the current active node the instance is currently running?
  4. Who are the active or possible owners available?
  5. Which are the shared disks we are accessing?
  6. The various settings for SQL Server Cluster resource? etc

Even though these look simple, ask your DBA and you will be surprised with their expression :) – trust me. In this blog post, let me call out some of the important information we need to collect ahead of time. This is not exhaustive, but I am sure this gives you a good start.

First connect to SQL Server Management Studio, connect to Server and open a Query window. Make sure you document these in each of your environments.

1. Version and Edition

SELECT @@VERSION;

SELECT SERVERPROPERTY(‘ProductVersion’) AS ProductVersion,

SERVERPROPERTY(‘ProductLevel’) AS ProductLevel,

SERVERPROPERTY(‘Edition’) AS Edition

2. Find SQL Server Instance is Clustered

SELECT SERVERPROPERTY(‘IsClustered’);

We will get a value of 0 if the instance is not clustered, else we will get 1.

3. Which instance are we actively running

SELECT SERVERPROPERTY(‘ComputerNamePhysicalNetbios’)

4. Which nodes instance is running

SELECT NodeName, Status, status_description, is_current_owner

FROM sys.dm_os_cluster_nodes;

5. How to identify Shared Disks the instance can access

SELECT DriveName FROM sys.dm_io_cluster_shared_drives;

6. Current SQL Server Cluster resource settings

SELECT VerboseLogging,

SqlDumperDumpTimeOut, FailureConditionLevel, HealthCheckTimeout

FROM sys.dm_os_cluster_properties;

Some of these properties are used to set the values that affect failure detection, failure response times etc. Knowing our environment is important and as a professional DBA, it is critical for us to have the datapoints handy.

Inspired from Book content : “SQL Server 2012 AlwaysOn Joes 2 Pros®: A Tutorial for Implementing High Availability and Disaster Recovery using AlwaysOn Availability Groups” (Paperback, Kindle) is now available.

Continue reading...


 

SSMS: T-SQL Debugger Shortcuts August 30th, 2013

Vinod Kumar

Earlier this year I wrote about SQL Server 2012: Debugger basics. I felt that post had a lot of fun in the new debugger features. Having said that, this post can be seen as an continuation to the previous post. Here I call out all the shortcuts available with SQL Server Management Studio when it comes to using Debugger features.

As I write this, I would also want to know how many of you have used the debugger feature in your environments?

Action

Shortcut

Start or continue debugging

ALT+F5

Show next statement

ALT+NUM

Implement the Run To Cursor command

CTRL+F10

Display the QuickWatch dialog box

CTRL+ALT+Q

Toggle breakpoint

F9

Enable breakpoint

CTRL+F9

Stop debugging

SHIFT+F5

Step into

F11

Step over

F10

Step out

SHIFT+F11

Delete all breakpoints

CTRL+SHIFT+F9

Display the Breakpoints window

CTRL+ALT+B

Break all

CTRL+ALT+BREAK

Break at function

CTRL+B

Display the Watch 1 window

CTRL+ALT+W, 1

Display the Watch 2 window

CTRL+ALT+W, 2

Display the Watch 3 window

CTRL+ALT+W, 3

Display the Autos window

CTRL+ALT+V, A

Display the Locals window

CTRL+ALT+V, L

Display the Immediate window

CTRL+ALT+I

Display the Call Stack window

CTRL+ALT+C

Display the Threads window

CTRL+ALT+H

Display the Parallel Stacks window.

CTRL+SHIFT+D, S

Display the Parallel Tasks window

CTRL_SHIFT+D, K

This is interesting because SQL Server Management Studio is based out of Visual Studio shell and hence many Visual Studio shortcuts work inside SQL Server Management Studio too as you can see above. Do let me know if I missed out on any of the interesting shortcut for debugger.

Continue reading...


 

SQL Server DATE and TIME formats August 26th, 2013

Vinod Kumar

This topic is very close to my heart when I talk with customers almost daily. Customers when working in applications have a constant doubt of what format to use. There is no one right method, but here is one suggestion I have for you. Use one consistent format throughout your application especially when you plan to store inside SQL Server. Don’t try to mix and match formats – this is because whenever you need to compare dates, you will be doing an explicit CAST or CONVERT function in all your WHERE clauses. So in this blog, let me bring out the list of formats and the sample output for your reference.

Date Format

SQL Statement

Output

MM/DD/YY

CONVERT(VARCHAR(8), GETDATE(), 1)

09/29/12

YY.MM.DD

CONVERT(VARCHAR(8), GETDATE(), 2)

12.09.29

DD/MM/YY

CONVERT(VARCHAR(8), GETDATE(), 3)

29/09/12

DD.MM.YY

CONVERT(VARCHAR(8), GETDATE(), 4)

29.09.12

DD-MM-YY

CONVERT(VARCHAR(8), GETDATE(), 5)

29-09-12

DD Mon Y

CONVERT(VARCHAR(9), GETDATE(), 6)

29 Sep 12

Mon DD, Y

CONVERT(VARCHAR(10), GETDATE(), 7)

Sep 29, 12

MM-DD-YY

CONVERT(VARCHAR(8), GETDATE(), 10)

09-29-12

YY/MM/DD

CONVERT(VARCHAR(8), GETDATE(), 11)

12/09/29

YYMMDD

CONVERT(VARCHAR(6), GETDATE(), 12)

120929

Mon DD YYY

CONVERT(VARCHAR(20), GETDATE(), 100)

Sep 29 2012 9:51AM

MM/DD/YYYY

CONVERT(VARCHAR(10), GETDATE(), 101)

09/29/2012

YYYY.MM.DD

CONVERT(VARCHAR(10), GETDATE(), 102)

2012.09.29

DD/MM/YYYY

CONVERT(VARCHAR(10), GETDATE(), 103)

29/09/2012

DD.MM.YYYY

CONVERT(VARCHAR(10), GETDATE(), 104)

29.09.2012

DD-MM-YYYY

CONVERT(VARCHAR(10), GETDATE(), 105)

29-09-2012

DD Mon YYY

CONVERT(VARCHAR(11), GETDATE(), 106)

29 Sep 2012

Mon DD, YYYY

CONVERT(VARCHAR(12), GETDATE(), 107)

Sep 29, 2012

HH:MM:SS

CONVERT(VARCHAR(8), GETDATE(), 108)

09:51:05

Mon DD YYYY HH:MI:SS:MMMAM (or PM)

CONVERT(VARCHAR(26), GETDATE(), 109)

Sep 29 2012 9:51:05:910AM

MM-DD-YYYY

CONVERT(VARCHAR(10), GETDATE(), 110)

09-29-2012

YYYY/MM/DD

CONVERT(VARCHAR(10), GETDATE(), 111)

2012/09/29

YYYYMMDD

CONVERT(VARCHAR(8), GETDATE(), 112)

20120929

DD Mon YYYY HH: MM:SS:MMM(24h)

CONVERT(VARCHAR(24), GETDATE(), 113)

29 Sep 2012 09:51:05:910

HH:MI:SS:MMM(24H)

CONVERT(VARCHAR(12), GETDATE(), 114)

09:51:05:910

YYYY-MM-DD HH:MI:SS(24h)

CONVERT(VARCHAR(19), GETDATE(), 120)

2012-09-29 09:51:05

YYYY-MM-DD HH:MI:SS.MMM(24h)

CONVERT(VARCHAR(23), GETDATE(), 121)

2012-09-29 09:51:05.910

YYYY-MM-DDTHH:MM:SS:MMM

CONVERT(VARCHAR(23), GETDATE(), 126)

2012-09-29T09:51:05.910

DD/MM/YYYY HH:MI:SS:MMMAM

CONVERT(VARCHAR(25), GETDATE(), 131)

14/11/1433 9:51:05:910AM

Here are few more conversion functions for your reference.

Function Name

Description

Syntax

DATEFROMPARTS

Returns a DATE value for the specified year, month and day.

DATEFROMPARTS( <year>, <month>, <day> )

DATETIME2FROMPARTS

Returns a DATETIME2 value for the specified date and time and precision.

DATETIME2FROMPARTS( <year>, <month>, <day>, <hour>, <minute>, <seconds>, <fractions>, <precision> )

DATETIMEFROMPARTS

Returns a DATETIME value for the date and time.

DATETIMEFROMPARTS( <year>, <month>, <day>, <hour>, <minute>, <seconds>, <milliseconds> )

DATETIMEOFFSETFROMPARTS

Returns a DATETIMEOFFSET value for the date and time and with the offsets and precision.

DATETIMEOFFSETFROMPARTS( <year>, <month>, <day>, <hour>, <minute>, <seconds>, <fractions>, <hour_offset>, <minute_offset>, <precision> )

SMALLDATETIMEFROMPARTS

Returns a SMALLDATETIME value for the date and time.

SMALLDATETIMEFROMPARTS( <year>, <month>, <day>, <hour>, <minute> )

TIMEFROMPARTS

Returns a TIME value for the time and with the specified precision.

TIMEFROMPARTS( <hour>, <minute>, <seconds>, <fractions>, <precision> )

EOMONTH

Returns last day of the month for the date.

EOMONTH( <start_date> [, <month_to_add>] )

  1. <year> – Integer specifying a year.
  2. <month> – Integer specifying a month.
  3. <day> – Integer specifying a day.
  4. <hour> – Integer specifying hours.
  5. <minute> – Integer specifying minutes.
  6. <seconds> – Integer specifying seconds.
  7. <milliseconds> – Integer specifying milliseconds.
  8. <fractions> – Integer specifying fractions. This parameter depends on the <precision> parameter.
  9. <hour_offset> – Integer specifying the hour portion of the time zone offset.
  10. <minute_offset> – Integer specifying the minute portion of the time zone offset.
  11. <precision> – Integer specifying the precision of the DATETIME2 value to be returned. For example, if <precision> is 7, then each fraction represents 100 nanoseconds.
  12. <start_date> – Date for which to return the last day of the month.
  13. <month_to_add> – Integer specifying the number of months to add to <start_date>.

Hope this will be a good start and reference when it comes to using date and time. Is there anything else that you have done more with Date functions? Do let us know.

Continue reading...


 

Quick Comparison on RAID Levels August 21st, 2013

Vinod Kumar

This blog post is inspired based on tons of customers I meet daily as part of my job. Many customers often ask me which RAID levels to use with SQL Server. Well, the basics and best practices vary between types of files and how the access happens using SQL Server. As a quick recap, data files needs random data access while Log files need sequential access. And then we need tempdb on the fastest drive. All these are general rules of thumb and not something written on stone.

Here is the quick recap of what I suggest based on various dimensions for each of the RAID levels.

RAID Levels

RAID-0

RAID-1

RAID-5 or

RAID-6

RAID-10

Reliability

Lowest

No fault tolerance results in data loss

Very good

Even better with duplexing

Good

Can tolerate single machine fault

Excellent

Storage Efficiency

100%

50%

>50% <100%

50%

Random Read

Excellent

Fair

Worst of the RAID Levels

Fair

Very good

Random Write

Excellent

Fair

Very good

Very good

Sequential Read

Excellent

Very Good

Comparable to a single drive

Good

Generally, better with smaller stripe sizes

Good

Sequential Write

Excellent

Very Good

Better than other RAID levels

Fair

Good

Cost

Lowest

Moderate

Relatively high cost due to redundant drives

Moderate

High

These are important considerations before we build our very own RAID levels as an Administrator. Obviously, the choice always is between RAID-5 or RAID-10. Please use the above table as a quick reference and do let me know if you found more dimensions.

Continue reading...