AboutSQL Server, Analytics, .Net, Machine Learning, R, Python Archives
About Me
Mitch Wheat has been working as a professional programmer since 1984, graduating with a honours degree in Mathematics from Warwick University, UK in 1986. He moved to Perth in 1995, having worked in software houses in London and Rotterdam. He has worked in the areas of mining, electronics, research, defence, financial, GIS, telecommunications, engineering, and information management. Mitch has worked mainly with Microsoft technologies (since Windows version 3.0) but has also used UNIX. He holds the following Microsoft certifications: MCPD (Web and Windows) using C# and SQL Server MCITP (Admin and Developer). His preferred development environment is C#, .Net Framework and SQL Server. Mitch has worked as an independent consultant for the last 10 years, and is currently involved with helping teams improve their Software Development Life Cycle. His areas of special interest lie in performance tuning |
Tuesday, February 27, 2018Blog moved…I’ve moved this blog and content to here. Thursday, February 22, 2018Postgres ConfigurationConfiguration file locations:
Go to bottom of .conf file, and add this line:
Then create file ‘postgresql.custom.conf’ in the same directory and place your customised configuration settings in it. Any settings set in the custom file will override those in the main config. Navigate to pgtune and enter the required information, and pgtune will generate custom settings based upon total RAM size and intended use etc: Copy the generated settings into file ‘postgresql.custom.conf’:
Restart Postgres. Further reading on Postgres performance: http://www.craigkerstiens.com Wednesday, February 14, 2018Do you Encrypt your Remote Connections to SQL Azure Databases?If you’re not encrypting connections to SQL Azure (or any remote SQL Server instance), then you probably should. Encrypted connections to SQL Server use SSL, and that is about as secure as you can get (currently). [Remember: SSL protects only the connection, i.e. the data as it is transmitted ‘on the wire’ between the client and SQL Server. It says nothing about how the data is actually stored on the server]. SSMSWhen you open SSMS’s ‘Connect to Server’ dialog, click the bottom right ‘Options’ button, and make sure you tick the checkbox ‘Encrypt Connection’: SQLCMDEnsure you add the -N command line option. The -N switch is used by the client to request an encrypted connection. This option is equivalent to the ADO.net option e.g.
Linked ServersWhen creating a linked server to SQL Azure, the @provstr parameter must be set to 'Encrypt=yes;’:
ADO.NET Connection stringsAdd “ [Remember: don’t distribute passwords by sending as plaintext over the Internet, i.e. don’t email passwords! ] Tuesday, February 06, 2018Installing TensorFlow with GPU support on Windows 10If you have a high end NVidia graphics card and you’re investigating data science with Keras+Tensorflow, then you obviously want Tensorflow to take advantage of your GPU (training times for deep neural networks can be 10 – 15 times faster even when compared to the latest CPUs). Getting it all working can be tricky: I found this guide that explains the steps: Installing TensorFlow with GPU on Windows 10 Here’s another: How to run TensorFlow with GPU on Windows 10 in a Jupyter Notebook Thursday, January 25, 2018Free e-Learning Books at PacktJust came across this link at Packt: https://www.packtpub.com/packt/offers/free-learning Wednesday, January 24, 2018The Zen of PythonI’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):
Tuesday, December 19, 2017More SQL Server Trace Flags
Another SQL Server Trace Flags resource (in addition to the Microsoft one).
There are a bunch of SQL Server resources hosted there, SQL Server Kit. Saturday, December 16, 2017SQLDiagCmd UpdatedI’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: Thursday, December 14, 2017A recursive C# functionI 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!] Tuesday, November 14, 2017SQL Server: A more useful CXPacket Waits...
Starting with the upcoming SQL Server 2017 CU3 and SQL Server 2016 SP2 releases, CXPACKET waits are split into an actionable wait (CXPACKET) and a negligible wait (CXCONSUMER).
(these wait types are already present in Azure SQL Database). Making parallelism waits actionable Wednesday, November 01, 2017SQL Server Trace Flags
Microsoft have published a useful list of all SQL Server trace flags in a single location:
DBCC TRACEON - Trace Flags In the past, some of these were poorly documented or hard to find. Wednesday, October 25, 2017Shared Memory Protocol is not Supported on SQL Server Failover ClustersI 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:
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:
e.g. Force connection to use the TCP protocol:
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.:
Tuesday, October 17, 2017SSMS 17.3 has XE Profiler built-inNew 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 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:
Friday, October 13, 2017SSAS: Turn Off Flight RecorderA quick and easy SSAS optimisation: turn off flight recorder:
http://byobi.com/2016/01/ever-wondered-whats-captured-in-the-ssas-flight-recorder/ |
ContactMSN, Email: mitch døt wheat at gmail.com LinksFavorites
Blogs |