Problems displaying this newsletter? View online.
SQL Server Central
Featured Contents
Question of the Day
Featured Script
Redgate SQL Provision
The Voice of the DBA
 

Server Hardware or Coffee?

This editorial was originally published on 3 Nov, 2016. It is being republished as Steve is traveling.

I actually chuckled out loud in my office recently. I was reading a piece about Expensify and how they learned some lessons from their database architecture over time. It's a good piece, but at one point the author talks about two Dell servers. There's a dual core 2.8Ghz CPU with 500GB of storage for US$700. This could be upgraded to an 8 core, 3.7GHz CPU with 64GB of RAM and 10TB of storage for around US$3,200. However, the author says don't be cheap. Why?

That's less than the company spends on coffee each month.

I thought that line was funny, and it proves a point. It can be easy to think about the cost of hardware as being high because it's a large ticket item for most of us. When you view it in the context of the scale of business, then it's cheap. If any of us spent $3000 a month on food, we might not think $3000 for a server is expensive. When you look at the cost of your labor that might need to babysit an underpowered system, a few more thousand dollars seem like a bargain for memory or CPU resources.

The author also notes that while EC2 charges for managing your systems, it's still a steal, and for many of us, capacity isn't a big issue. We do find that one single system runs most of our workload. If it doesn't, then we could buy more, or a second system, for a relatively low cost. At least, compared to the rest of our business.

There certainly are some of you that have a large workload, or you have spent substantially more on server hardware. I think many of my systems have been tens of thousands of dollars, but there are good points to be made. Today's computers are very powerful, and even though we have more data, a single server is likely able to handle many workloads.

If we write good code.

That's a big caveat, but training your staff to code better, having them spend time learning to query hierarchies more efficiently or quickly splitting strings, can pay off with much happier servers and customers. While I am a fan of using hardware to avoid spending too much time tuning queries, I also think continuously improving the skills of your development staff is much more important and might be the best IT investment you can make. Assuming, of course, that you treat them well and they enjoy working for your management staff.

Steve Jones - SSC Editor

Join the debate, and respond to today's editorial on the forums

Redgate Database Devops
 
  Featured Contents

Performance Tuning Using Extended Events: Part 3

bdavey from SQLServerCentral.com

Identifying Performance Tuning Opportunities Using Extended Events: Part 3 Aggregate Report

Controlling how SQL Prompt Formats your Code: The Knobs and Dials

Additional Articles from Redgate

Phil Factor explores and discusses the current state of the art in SQL Formatting, as done automatically by SQL Prompt.

Overview of Azure SQL Database Networking

Additional Articles from MSSQLTips.com

Learn about the range of connectivity options available for Azure SQL Database deployments.

From the SQL Server Central Blogs - The Gift of the SPN

SQLRNNR from SQL RNNR

Every once in a while there is an extremely valuable tool that comes along. While the footprint and use frequency of this tool may not be that big, the...

From the SQL Server Central Blogs - SQLSaturday Orlando Notes – Owning Stuff

Andy Warren from SQLAndy

One of the choices you make as an organizer is whether to buy anything that you want use or give away at the end of the event. Owning nothing...

 

  Question of the Day

Today's question (by Steve Jones - SSC Editor):

 

The PowerShell String

I have this code:
$name = 'Steve'
write-host( "My name is $name")
What is output from the write-host() statement?

Think you know the answer? Click here, and find out if you are right.

 

 

  Yesterday's Question of the Day (by crow1969)

What is the datatype?

I came across this (anonymised) view in a recent code review:

create view dbo.QOTD
as
select null as ummm_what
go

I was curious as to what the datatype for the column would be, and ran sp_help on the view.  What datatype does SQL Server pick for the view?

Answer: int

Explanation: The NULL columns were meant as placeholders until real data was available, but there may be some implications as far as how downstream systems may handle the column (I'm looking at you, SSIS).  This behavior appears to be rooted in the SELECT statement, rather than the creation of the view.  Creating a temp table by SELECT ... INTO will also cause datatype-less columns to be defined as INT.  In short, the following would not work:

select null as placeholder into #temp

insert into #temp (placeholder) values ('hello')

Discuss this question and answer on the forums

 

Featured Script

Stored Procedures Inside Stored Procedures

chadswt from SQLServerCentral

See a template for a procedure in a procedure.

DROP PROCEDURE IF EXISTS dbo.RunProcInProc
GO
CREATE PROCEDURE dbo.RunProcInProc
AS
SET NOCOUNT ON;
/** CHAD MILES 12/5/2019 **/
DROP TABLE IF EXISTS #DataLog
DROP PROCEDURE IF EXISTS #Success
DROP PROCEDURE IF EXISTS #Failure
CREATE TABLE #DataLog
(ProcessStep VARCHAR(50),
StartDate DATETIME DEFAULT GETDATE(),
StatusDesc VARCHAR(20))
DECLARE @SuccessQry VARCHAR(MAX), @FailureQry VARCHAR(MAX)

SET @SuccessQry = REPLACE('
CREATE PROCEDURE #Success
@ProcessStep VARCHAR(50)
AS
SET NOCOUNT ON;
INSERT INTO #DataLog
(ProcessStep, StatusDesc)
VALUES
(@ProcessStep, |Success|)', '|', CHAR(39))

SET @FailureQry = REPLACE('
CREATE PROCEDURE #Failure
@ProcessStep VARCHAR(50)
AS
SET NOCOUNT ON;
INSERT INTO #DataLog
(ProcessStep, StatusDesc)
VALUES
(@ProcessStep, |Failure|)', '|', CHAR(39))

EXEC (@FailureQry)
EXEC (@SuccessQry)

EXEC #Success @ProcessStep = 'Do Something Right'

EXEC #Failure @ProcessStep = 'Do Something Wrong'

EXEC #Success @ProcessStep = 'Back On Track'
SELECT * FROM #DataLog
GO

EXEC dbo.RunProcInProc

More »

 

Database Pros Who Need Your Help

Here's a few of the new posts today on the forums. To see more, visit the forums.


SQL Server 2017 - Administration
Composite non-clustered index: will it still work if I use only one index col) ? - If I have an index  called idxT1_C1C2C3  on table T1 that is non-clustered non-unique non covering,  has 3 colums C1, C2, C3 in same order, and I run a query SELECT    FROM T1 WHERE C1 = 'value' can such query be expected at all to still use the idxT1_C1C2C3  index? Or […]
SQL Server 2016 - Administration
Why columnstore indexes are not very popular? - I noticed that many SQL Server shops do not even consider or discuss introducing columnstore indexes or in-memory columnstore indexes. While trying to solve performance issues with many queries in many other painful ways, this option is not even on the plate of DBAs and Developers and product owners agenda in many today's IT shops […]
SQL Server Agent replacement - Friends, I have a server, inherited, SQL Server with more than 150 jobs using ETLs, direct invokations for SPs, etc. This server, only job  is executing those jobs, because there are no user databases associated. I am just thinking, if could be a better way to program those tasks or my only option is to […]
SQL Server Pivot Table on two columns renaming one column - I've the following source table: --------------------------------------------------------------- | Id | GroupName | RuleName | RuleText | RuleValue | +-------------------------------------------------------------+ | 1 | Group1 | Exclude1 | Excluded (Reason1) | 1 | +-------------------------------------------------------------+ | 1 | Group1 | Exclude2 | Excluded (Reason2) | 1 | +-------------------------------------------------------------+ | 1 | Group1 | Exclude3 | Excluded (Reason3) | 1 […]
SSRS Email Log review - Hi gang, I need some guidance: I have a SQL server 2016 standard instance set up with SSRS on a remote server environment (running NetForum) I have been tasked with helping the report team allow scheduled reports to get sent via email. Seems simple, right? We are using Office 365. I set up an email […]
can I use Stretch database to migrate the database to azure. - HI Guys,   can I use Stretch database to migrate the database to azure.? how ?
Performance difference - I have a user sql from 3rd party started running terrible on prod server.  has 128 gig ram, 4 procs plenty of disk space.  It basically runs forever and buries tempdb (yes it is a cte and it is ugly).  take a backup of prod drop on uat server, 2 procs 16 gig of ram, […]
List of INDEX with create and DROP script should not include any constrains P/F - Hello Sir.. Please help me the script.. From entire database i want List of INDEX with CREATE and DROP INDEX script, But in this should not include any constrains like primary keys and Foreign keys. Please help any one have the script like this? Thank you..
SQL Server 2016 - Development and T-SQL
Pivot Query - /* I'm trying to write a query that uses a list of dates as a header and lists, lets say, employees and if they were in training on that day. I've made a start, but I can't get a pivot working Note: This, by necessity, is a made up scenario similar to what I'm trying […]
SQL 2012 - General
Uninstall SQL without uninstalling SSRS - We have a non-prod server that (long ago) we installed SQL Server on and we were only supposed to install the SSRS components. Long story short, corporate wants us to remove components we are not using due to security concerns. Nevermind that we've disabled the services, there are concerns. I'm not sure there's a good […]
SQL Server 2008 - General
weird sql problem. - Hi all, I´m not sure what I´m doing bad with this query: Situation: I need to select all the purchase order lines(poitem_all) that not exists in the table of requisition lines (preqlines). select po_num from poitem_all results with all the  purchase orders (po_num) for the table poitem_all. Ok. Then for select only that not exists […]
T-SQL (SS2K8)
plantation shutter exterior or interior grade - Here is a recent inquiry we received about an oval window, and our response with the customer: Customer Request– Hi there. I have an oval bathroom window for which I’m looking for Plantation shutters the hieght is 30? and the width is 40-1/2? I’m not sure if I prefer the fan -like, or horizontal louvre […]
is there any better way to export SQL server data into csv file? - I have  questions of exporting SQL server data, the details as below, thanks! how to use BCP export data from SQL server (table or view or stored procedures) into a csv file (supposing the file path is d:\temp) ? is there any better way to fast export SQL server data into csv file ?
Reporting Services
SSRS log error - We have a SQL server reporting server installed both database engine and SSRS service 2017 on it. The database only hosts the reporting database. I found a lot of repeating errors in the file: C:\Program Files\Microsoft SQL Server Reporting Services\SSRS\LogFiles: 2019-12-10 15:00:04.0060|WARN|5|Exception deleting expired log fileSystem.IO.IOException: The process cannot access the file 'D:\Program Files\Microsoft SQL […]
ssrs 2012 column headers repeat on each page while the user is running the rpt - My issue I want to have column headers repeat on each page while the user is running (accessing) the ssrs report. I just started to work with ssrs 2012 reports. Recently I have workd with ssrs 2010 and ssrs 2008 reports. In the past when working with ssrs 2010 reports, I would select the 'advanced […]
 

 

RSS FeedTwitter

This email has been sent to {email}. To be removed from this list, please click here. If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. This newsletter was sent to you because you signed up at SQLServerCentral.com.
©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
webmaster@sqlservercentral.com

 

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -