Press "Enter" to skip to content

Curated SQL Posts

Giving 110%

Published 2015-11-23 by Kevin Feasel

SQL Sasquatch shows that his computers go up to 11:

I trust the utilization reported by “Processor Info”.  Note that the greatest reported “Resource Pool Stats” utilization (approaching 120%) is when total “Processor Info” utilization is near 100% across all 12 physical/24 logical cores.  Nominal rating of the core is 3.06 GHz, top SpeedStep is 3.46 GHz.  That would give a maximum ratio of 3.46/3.06 = 113%, which is still under the number reported by SQL Server (for Default pool alone, I’ll add).  Even if the numbers made it seem possible that SpeedStep was responsible for more than 100% utilization reported by SQL Server, I don’t think SpeedStep is the culprit.  The older Intel processors were by default conservative with SpeedStep, to stay well within power and heat envelope.  And no-one’s been souping this server up for overclocking 🙂

So… if my database engine will give 110% (and sometimes more…) I guess I better, too.  🙂

Math is hard.

Comments closed

Data Versus Domain Design

Published 2015-11-23 by Kevin Feasel

Vladimir Khorikov talks domain-centric versus data-centric design:

With the domain-centric approach, on the other hand, programmers view the domain model as the most important part of the software project. It is usually represented in the application code, using an OO or functional language. Data (as well as other notions such as UI) is considered to be secondary in this case:

Each of the approaches brings its own pros and cons, as well as some differences in the way developers address common design challenges. Let’s elaborate on that.

Khorikov is domain-centric, whereas I am data-centric.  My justification is as follows:  20 years from now, the most likely scenario is that your application has been re-written three or four times, whereas my database is still chugging along.  Therefore, we should design in ways which make it easier to maintain correct data.

Comments closed

Extended Properties

Published 2015-11-23 by Kevin Feasel

Steve Jones shows extended properties:

Once I click Properties, I get a dialog with a lot of items on the left. The bottom one is for Extended Properties, with a simple add/edit/delete grid. Here I can see the property(ies) I’ve added.

However this is cumbersome for me. I’d much rather find a way to query the information, which is what I need to do with an application of some sort. I’d think sp_help would work, but it doesn’t.

Extended properties, like Service Broker, was a great idea that floundered because there was never a good UI.  Given how much fighting Steve has to do to see one object’s properties, it’s no wonder they’re so relatively unused.  And that’s a shame, because with the right tooling, they can be a great way of documenting data structures.

Comments closed

One Step Closer

Published 2015-11-23 by Kevin Feasel

Angela Henry shows us that SQL Server Data Tools is kinda-sorta integrated directly into Visual Studio’s installation now:

Next I wanted to see if you got all these same project types with an install of Visual Studio. They announced at Summit that “SSDT” would now be “included” with Visual Studio. So I went out and downloaded Visual Studio (CTP3, Community Edition, i.e., free) from here. And look what shows up on the install features list, there it is in black and white, Microsoft SQL Server Data Tools, almost too good to be true.

Well, we all know that if something seems too good to be true, then it usually is. This is no exception.  Let’s see if you can pick out the reason for my disappointment in the picture below.

It’s one step closer, at least.

Comments closed

More On Temporal Tables

Published 2015-11-20 by Kevin Feasel

Randolph West has another nice post on temporal tables, in which he lets the cat out of the bag:

Look at the log records. They are identical to before. In fact, because a trigger is called in the same implicit transaction as the UPDATE statement, we didn’t even need to wrap the INSERT statement in a transaction.

What have we learned?

Temporal tables are doing exactly what an update or delete trigger (or stored procedure) would do, based on what we saw in the log records.

This is an in-depth look at what, exactly, is happening when temporal tables get updated.

Comments closed

PowerShell-Based Health Checks

Published 2015-11-20 by Kevin Feasel

Omid Afzalalghom discusses an open-source tool using PowerShell to read various DMVs and give you a basic health check:

PowerShell is an ideal tool for doing health-checks of a collection of SQL Server instances, and there are several examples around, but few acknowledge the fact that individual DBAs have their own priorities for tests, and need something easily changed to suit circumstances. Omid’s Healthcheck allows tests to be SQL or PowerShell and requires only adding, altering or deleting files in directories.

Grab the tool from his GitHub repo.

Comments closed

The Future Of Datazen

Published 2015-11-20 by Kevin Feasel

Meagan Longoria is thinking about the future of Datazen now that Microsoft has acquired them:

This new report manager means the current administration of Datazen (hubs and dashboard groups, etc.) will likely change to conform to more of the SSRS or Power BI paradigms.  As far as I know, there aren’t yet solid details on exactly how this will work. In the Microsoft-led sessions at PASS Summit, they also mentioned that there will be migration tools for those already using Datazen that want to take advantage of this new platform.
So if you are considering Datazen but were worried about its future, there is no need to worry. If you are implementing Datazen now, you can feel better about enhancement and upgrade opportunities with the promised migration tool.

Click through for a hypnotizing animated GIF.  Seriously, I needed to close the window after a couple of minutes because I kept watching it over and over and over.

Comments closed

Starting And Stopping Local Instances

Published 2015-11-20 by Kevin Feasel

Slava Murygin shows ways to simplify starting and stopping SQL Server services on local instances:

Step 3: Easily Stop SQL Server Service.

That is very possible you performed some very heavy and memory intense operation by your local SQL Server instance and it ate all your free memory. You do not need it anymore, but SQL Server won’t easily give memory back.
The easiest way to claim all your memory is to stop your SQL Server.
There are some easy ways doing it:
– First way us using SQL Server Management Studio. You just have to do a right click on your local SQL Server instance and choose “Stop”.

The use case for Slava’s advice is a scenario in which you have SQL Server installed on a local machine with very little RAM.

Comments closed