admin

The Zen of Python

by

I’ve recently been learning Python with the goal of using it alongside R for data science. It’s got a lot going for it as a language and the package (library) support covers just about every domain you can think of.

Many of the ‘C’ like languages seem intent on creating too much complexity for no other reason than ‘you can’, but Python takes a more pragmatic approach.

I particularly like the Zen of Python (PEP 20):

The Zen of Python, by Tim Peters

Beautiful is better than ugly.
Explicit is better than implicit.
Simple is better than complex.
Complex is better than complicated.
Flat is better than nested.
Sparse is better than dense.
Readability counts.
Special cases aren’t special enough to break the rules.
Although practicality beats purity.
Errors should never pass silently.
Unless explicitly silenced.
In the face of ambiguity, refuse the temptation to guess.
There should be one– and preferably only one –obvious way to do it.
Although that way may not be obvious at first unless you’re Dutch.
Now is better than never.
Although never is often better than *right* now.
If the implementation is hard to explain, it’s a bad idea.
If the implementation is easy to explain, it may be a good idea.
Namespaces are one honking great idea — let’s do more of those!

SQLDiagCmd Updated

by

I’ve updated SQLDiagCmd, my standalone executable for running any or all of Glenn Berry’s excellent SQL Server DMV diagnostic scripts.

As well as being able to target multiple servers and multiple databases, it now also has the option to exclude specified queries from being executed (such as those that might take some time to execute on large very databases or busy server instances).

The source code is available on GitHub and you can download the executable directly from these links:

SQLDiagCmd.zip

SQLDiagUI.zip

A recursive C# function

by

I was searching through email today looking for a LINQPad snippet that a colleague, James Miles, wrote some time ago, one which we used to generate the scripts for a production SQL Server database + transaction log point in time restore after IT had a little SAN mishap!

In doing so, I came across this gem from James: Solving Puzzles in C#: Poker Hands, which is not just a great example of writing a recursive function but of problem solving in general. [Where I used to work, we often used to have a Friday puzzle where I tried to come up with or find puzzles that wouldn’t be easy to solve by brute force.  This was one of the many times I was thwarted by James and others!]

Shared Memory Protocol is not Supported on SQL Server Failover Clusters

by

I was recently trying to work out why SSAS installed on the same server as SQL Server would not use shared memory for its processing connections. It may be obvious to some people, but an internet search turns up surprising few references: the Shared Memory Protocol is not Supported on SQL Server Failover Clusters.

On a standard SQL Server instance, the Shared Memory protocol can be used when a client is running on the same computer as the SQL Server instance and the Shared Memory Protocol is enabled in SQL Server’s network protocols. (You can check the status of the enabled protocols using SQL Server Configuration Manager).

sys.dm_exec_connections will show you which net transport a client connection is using:

SELECT net_transport FROM sys.dm_exec_connections WHERE session_id = @@SPID;

You can force a client connection to use a specific protocol by prefixing the Server name in the connection string with one of these modifiers:

  • TCP: tcp:
  • Multiprotocol = rpc:
  • Shared Memory = lpc:

e.g. Force connection to use the TCP protocol:

Server=tcp:MyServerName;Database=MyDB;Trusted_Connection=True;

In addition, you can force the client connection to use the Shared Memory protocol by using (local) as the server name. You can also use localhost or a period (.) e.g.:

Server=(local);Database=AdventureWorks;Trusted_Connection=True;

https://support.microsoft.com/en-au/help/313295/how-to-use-the-server-name-parameter-in-a-connection-string-to-specify

SSMS 17.3 has XE Profiler built-in

by

New to SQL Server Manager Studio (SSMS) 17.3 is the XE Profiler. This is Profiler-like functionality built-in to SSMS:

SSMS 17.3 has Profiler built-in

image

Just double-click either of the two entries to create a live trace window (built on the SSMS XE “Watch Live Data” functionality).  The event sessions that will be created are named:

  • Standard:  QuickSessionStandard
  • TSQL:        QuickSessionTSQL

SSAS: Turn Off Flight Recorder

by

A quick and easy SSAS optimisation: turn off flight recorder:

SQL Server Analysis Services Flight Recorder provides a mechanism to record server activity into a short-term log. Information captured by Flight Recorder can be helpful for troubleshooting specific issues, however the load placed on the server when capturing the snapshots and trace events can have a small impact on overall performance.  For optimal performance the flight recorder should be disabled unless attempting to capture diagnostic information relevant to troubleshooting a specific problem.

https://support.microsoft.com/en-au/help/2128005/flight-recorder-eanbled-for-sql-server-analysis-services

http://byobi.com/2016/01/ever-wondered-whats-captured-in-the-ssas-flight-recorder/

SQL Server: Do You Have a Poorly Performing Query you can’t Explain?

by

If you are running a SQL Server version prior to SQL Server 2016, and you have a query whose plan just doesn’t seem right and you can’t explain it, try running it with trace flag 4199

SELECT SomeColum
FROM SomeTable
OPTION(QUERYTRACEON 4199)

It enables all the query optimiser hot fixes present in your applied SP and CU version.
Many DBAs enable this trace flag globally (at the instance level).
SQL Server 2016 will automatically enable all prior version query optimiser hot fixes.

SQL Server query optimizer hotfix trace flag 4199 servicing model
SQL Server 2016: The Death of the Trace Flag