casinos

Posts Tagged ‘Tips and Tricks’

Creating happiness for yourself September 9th, 2013

Vinod Kumar

For every minute you are angry you lose sixty seconds of happiness ― Ralph Waldo Emerson

Happiness2Happiness is a subjective topic and relative to individuals. Every time I meet someone, they want to improve happiness in their lives and they are completely confused. Happiness is a state of mind, it is how we perceive happiness to be. For me happiness is something you learn from inner contentment, learn from your childhood, learn from your schools hard times and assume from people around you via their inner wisdoms. As you define your inner happiness try to keep your mind open, raw, simple, insightful, directional and actionable.

In my opinion there is no point in keeping this thought abstract and elusive thing that it cannot be achieved. It should be something that is out there which we can achieve yet right now not part of our life’s journey today. We can work hard day-in-day-out but we might feel happiness as an evasive thing. It is critical for us to include happiness into our lives in what ever shape we deem as stress buster. As I said before, it is more of a state of mind.

Time you enjoy wasting is not wasted time.

Make life meaningful

Make everything that you do count. Don’t forget to give your best where you need to give your best. It is always about playing your strengths and making a world that you want to live in. Make meaning out of things that you do day-in-day-out.

Happiness is when what you think, what you say, and what you do are in harmony ― Mahatma Gandhi

Scale your Happiness

Virtually there is no scale to measure happiness. In continuation to last point, hold to your values that make you happy. The simplest way to increase your happiness levels is to gradually increase your reasons to be happy. One of the dangerous levels is to compare your happiness levels with others.

Happiness is not a “What if” analysis

Our happiness is a decision of the mind. Don’t keep doing things because it is expected of you, but do things because you want to do and it makes you happy.

Don’t put an yardstick of “What if” scenarios to get into a happy state. A typical example is, “I would be happy if I get <<>>”. Don’t make your state of happiness too materialistic. Happiness made with such ideas is like climbing a mountain. Finally after reaching the pinnacle, our eye looks at the next mountain. This is the constant vicious circle of death of running behind matter – not happiness.

Sanity and happiness are an impossible combination ― Mark Twain

Be your own director of life

If you need to take a picture, you need to point your camera in that direction. So if you need to happiness, you need to work in that direction. You need to be happy doing simple things. You need to find happiness from simple things (smile of your child, playing a game you loved for years, Eating food your grandmother etc).

Happiness is not something ready made. It comes from your own actions ― Dalai Lama

As I sign off, remember happiness is a personal thing and what happiness to you is not the way others see as happiness.

The happiest people don’t have the best of everything. They just make the best of everything.

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...


 

Basics of SQL Server Execution Plans August 19th, 2013

Vinod Kumar

Chess was invented in 16th century, and is still the most popular board game currently, and in history. It has been played by millions of players, and continues to rise in popularity despite competition from all sorts of technologically advanced games. Each player gets eight pieces to make a total of sixteen pieces. There is a king, a queen, two rooks, two knights, two bishops, and eight pawns. The board is made up of the same black and white squares no matter where you play it, and every player has the same pieces. And yet out of all these games of chess, of the millions upon millions that have been played, no two have been identical.

How is this possible? Because chess is about strategy. It’s about being at your best, smart execution, planning your moves. All games of chess are different because different minds are playing them, and there are so many variables – 16 pieces! These multiple pieces need to work together and have a strategy to lead to one final execution plan.

Now let’s link this concept to SQL Server. There are many pieces that play a part in SQL, and they tend to stay the same from day to day and even from business to business. The main components are data, the CPU, memory, the SQL Server application, and the hard drive. However, as any DBA knows, this doesn’t mean SQL Server is ever the same from day to day. Why is this?

SQL changes all the time because while you’re working with the same “chess men.” Every execution plan will behave different because there will be different CPU cycles, different kind of joins, a different IO pattern, different memory.

Here is where the metaphor ends: there is a way to win at chess, but there are only ways to improve SQL Server, there is no trophy at the end. The only way to “win” is to use the best execution plan that has been drawn.

Some HIstory or background 

Query Optimization is often considered as a new subject but if you look back in time many of the present time best practices were discovered over 30 years ago. Here are few of the landmark research papers.

1978 – Nonprocedural Query Processing for Databases with Access Paths. Nancy D. Griffeth. ACM SIGMOD Conference 1978.

1978 – Distributed Query Processing in a Relational Data Base System. Robert S. Epstein, Michael Stonebraker, Eugene Wong. ACM SIGMOD Conference 1978

1979 – Parallel Processing of Relations: A Single-Assignment Approach. Joachim W. Schmidt. VLDB 1979.

1980 – A Methodology for Interpreting Tree Queries Into Optimal Semi-Join Expressions. D. M. Chiu, Y. C. Ho. ACM SIGMOD Conference 1980.

1980 – On Retrieval from a Small Version of a Large Data Base. Adrian Walker. VLDB 1980.

1981 – QUIST: A System for Semantic Query Optimization in Relational Databases. Jonathan J. King. VLDB 1981.

1981 – Optimal Semijoin Schedules For Query Processing in Local Distributed Database Systems. Mohamed G. Gouda, Umeshwar Dayal. ACM SIGMOD Conference 1981.

1982 – Query Processing for Distributed Databases Using Generalized Semi-Joins. Yahiko Kambayashi, Masatoshi Yoshikawa, Shuzo Yajima. ACM SIGMOD Conference 1982.

1983 – Estimating Block Transfers and Join Sizes. Stavros Christodoulakis. ACM SIGMOD Conference 1983.

The amount of research related to Query Processing has grown exponentially in last 30 years. History demonstrates that initially Joins were the point of the interests for early years Query Tuning Wizards.

Execution Plans

SQL Server execution plans are broken into two parts: query or compiled plans and execution contexts. Query/compiled plans are sharable by many users, whereas execution contexts are not. Query plans are stored in sys.dm_exec_cached_plans.

There are never more than two copies of the query plan in memory. There is one copy for all serial executions and there is possibly a second for all parallel executions. The parallel copy covers all parallel executions, regardless of their degree of parallelism.

The Query plan is generated at compile time and is reused until a recompile happens (due to a service restart, configuration change or user request). Query plan does not have any user or runtime information associated with it. The execution contexts contain runtime information like parameter values and are specific to the one session by the executing user.

Note The execution context data structures are reusable. When multiple users execute a stored procedure concurrently, each user will be given a separate execution context.

Multiple execution contexts can exist, especially in high concurrency environments. If a new user executes a procedure, and one of the existing execution contexts is not in use, it is reinitialized with the context for the new user.

Note The execution context maintains state information, such as the values of local variables/parameters for that execution that is specific to a given invocation of a query.

To improve plan stability, one deliberate choice from SQL Server 2005 was to keep the compiled plans in cache as long as possible. The architecture was changed so that compiled plans can keep pointers to the application execution context(s) for that plan.

Under memory pressure, the execution contexts are removed first since they can easily be regenerated. The compiled plans are only removed when there is still insufficient memory after removing the execution contexts. Hence in environments with high ad-hoc workloads, the plan cache can grow to a higher than expected value.

Query Plans are Fingerprints

The query plan should be read from right to left and top to bottom. A query plans shows:

  1. How data is accessed – which indexes are used; was seek or scan performed.
  2. How data is joined – order of tables joined and algorithm to join tables.
  3. How data is aggregated – aggregation algorithm used if query contains sum, avg etc
  4. Sequence of operations
  5. Use of temporary worktables – optimizer may create temporary work tables for example if the query contains a sort clause.
  6. Estimated rowcounts – how many rows the optimizer predicted would be returned by the query?
  7. Actual rowcounts – number of rows actually returned by the query.
  8. Use of parallelism – whether or not a parallel query plan was used.
  9. Operator level warnings from SQL Server 2012 and above.

Query and plan fingerprints are new from SQL Server 2008. The query fingerprint is generated by taking the SQL statement and running it through a hash function. If the SQL statements hash to the same value, they are probably the same SQL statement that only differ by literals.

The query fingerprint is exposed as the query_hash column in sys.dm_exec_requests, and sys.dm_exec_query_stats.

Note The query fingerprint is also exposed as the queryhash attribute of the XML query plan.

The query hash can be used to determine if the plan cache contains similar queries which cannot be auto-parameterized. This situation can occur more frequently when an application issues ad-hoc SQL statements.

SQL Server 2008 has added 2 DMVs to identify expensive procedures and triggers. To identify expensive procedures use sys.dm_exec_procedure_stats. This dmv can be used to identify expensive TSQL stored procedures, CLR stored procedures or Extended stored procedures. The type and type_desc columns indicate which type of procedure it is.

To identify expensive triggers use sys.dm_exec_trigger_stats. This DMV is functionally equivalent to the new sys.dm_exec_procedure_stats but it provides the data about the overall time taken inside of triggers only. This information is available for both SQL and CLR triggers.

Note Use sp_executesql when a single user might use the same batch multiple times and the parameters are known.

In SQL Server, using parameters or parameter markers in Transact-SQL statements increases the ability of the relational engine to match new SQL statements with existing, unused execution plans. If a SQL statement is executed without parameters, SQL Server parameterizes the statement internally to increase the possibility of matching it against an existing execution plan and this process is called as auto-parameterization.

Use auto-parameterization only for applications that cannot be easily modified. Auto-parameterization is useful in a very limited number of scenarios. Avoid designing applications that rely on auto-parameterization.

When the PARAMETERIZATION option (at Database settings) is set to FORCED, any literal value that appears in a SELECT, INSERT, UPDATE or DELETE statement is converted to a parameter during query compilation. Some queries may not be able to be parameterized.

Forced parameterization might improve performance for environments where high volumes of concurrent adhoc SQL statements are submitted like from an ERP or CRM systems.

Final words

I have been wanting to write on these concepts for a while and I hope this can be a good start to learn. These are fundamental notes on Execution plans and are these notes are a starting point. Keep learning and reading.

Continue reading...