Thursday, February 18, 2021

Revised Script: Indexing and looking for a cursor in an update statement

I ran a QC script over the results of yesterday's cursor counts table, and realized I had made a logical error.


Here's the QC scripts:

select count(*) as counted, 'photos' as [Source] from dbo.photos
union all
select count(*) as counted, 'photos-null votes' as [Source] from dbo.photos where tally is null
union all
select count(*) as counted, 'votes' as [Source] from dbo.votes;

select top 10 * from dbo.photos;
select top 10 * from dbo.votes;
select sum(tally) as TotalVotesCounted from dbo.photos;
select top 10 * from dbo.votes as v left join dbo.photos as p on p.id = v.photoID where p.tally is null





And here's the revised creation script:

set nocount on;
use enron;
go

if object_id('dbo.photos') is null
create table photos (id INT identity(1,1) primary key, photo varbinary(max), tally INT);
else
truncate table dbo.photos;
go

if object_id('dbo.votes') is null
create table votes(id INT identity(1,1) primary key, userID INT, photoID INT);
else
truncate table dbo.votes;
go

DECLARE @counter INT;  
SET @counter = 1;  
WHILE @counter < 500
   BEGIN  
      insert into dbo.photos(photo) values(rand() * 1000)
      SET @counter = @counter + 1  
   END;  
GO  

DECLARE @counter INT, @userid int, @photoid int, @msg varchar(256);  
SET @counter = 1;  
WHILE @counter < 50000
	BEGIN  
		SELECT @userid = rand() * 100;
		select @photoid = rand() * 1000;
		if (@photoid between 1 and 499)
		insert into votes(userid, photoid) values (@userid, @photoid)
		SET @counter = @counter + 1;
	END;  
GO  


 



Wednesday, February 17, 2021

Indexing and looking for a cursor in an update statement

 From this page:


http://www.artfulsoftware.com/infotree/qrytip.php?id=531&m=0


I wanted to see if, in SQL Server, this same mySQL query 

  1. Could be reproduced
  2. Could be sped up with an index
  3. Would show up as a cursor in the estimated execution plan for the update statement 


1. Reproduction

So, yes, this is a fairly simple script to reproduce.



set nocount on;
use enron;
go

if object_id('dbo.photos') is null
create table photos (id INT identity(1,1) primary key, photo varbinary(max), tally INT);
else
truncate table dbo.photos;
go

if object_id('dbo.votes') is null
create table votes(id INT identity(1,1) primary key, userID INT, photoID INT);
else
truncate table dbo.votes;
go

DECLARE @counter INT;  
SET @counter = 1;  
WHILE @counter < 500
   BEGIN  
      insert into dbo.photos(photo) values(rand() * 1000)
      SET @counter = @counter + 1  
   END;  
GO  

DECLARE @counter INT, @userid int, @photoid int, @msg varchar(256);  
SET @counter = 1;  
WHILE @counter < 50000
	BEGIN  
		SELECT @userid = rand() * 100;
		select @photoid = rand() * 1000;
		if (@photoid between 0 and 500)
		insert into votes(userid, photoid) values (@userid, @photoid)
		SET @counter = @counter + 1;
	END;  
GO  


However, in my version, I loaded a larger data set into the tables, as I wanted to have enough data to get a measureable difference when applying the index in step two.

Note also, that in my version, the values are randomly generated, but controlled, so they can only be inserted if they match a photo that we expect to actually have.


2. Indexing


Yes, the statement can be sped up with an index.


Here's the properties of the script, before index application:




Here's after applying the index:




It's a third of the cost. Win!


3. Cursor


There are loops - which is expected - but SQL Server doesn't dissolve into an explicit cursor to handle the update.




Friday, February 5, 2021

Installing SQL Server Configuration Manager when missing for remote management

Following up from the previous post, installing Client Tools Connectivity from the SQL Server 2019 DVD/ISO installed the SQL Server Configuration Manager feature into Server Manager & Computer Management within my management VM.






Thursday, February 4, 2021

There is insufficient system memory in resource pool 'default' to run this query

So while running a really generic restore to the SQL Server cluster instance sql1:

USE [master]

RESTORE DATABASE [enron] 
FROM  DISK = N'\\ServerE\iso\enron.bak'
WITH  FILE = 1, 
MOVE N'enron_Data' TO N'C:\Data\enron_Data.mdf', 
MOVE N'enron_Log' TO N'C:\Data\enron_Log.ldf', 
NOUNLOAD,  REPLACE,  STATS = 5

GO


The error, while descriptive, was interesting because it was "terse" :)

Msg 3224, Level 16, State 2, Line 2

Error: 3224, Severity: 16, State: 2. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.

Msg 3013, Level 16, State 1, Line 2

RESTORE DATABASE is terminating abnormally.

Msg 701, Level 17, State 123, Line 2

There is insufficient system memory in resource pool 'default' to run this query.


Completion time: 2021-02-04T03:18:26.1654581-08:00



Turns out, the RAM for the server was still set at it's installation default of 128 MB.




Once that was increased to 1024 MB, the problem magically went away. 





Tuesday, February 2, 2021

SSMS Error - An exception occurred while executing a Transact-SQL statement or batch.

I really don't like the errors like this. :(

I'm going to increase the RAM of the servers in the cluster and see if that helps.

TITLE: Microsoft SQL Server Management Studio
------------------------------

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

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

Error: 802, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
 (Microsoft SQL Server, Error: 802)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=15.00.2000&EvtSrc=MSSQLServer&EvtID=802&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

Monday, February 1, 2021

Can't see SQL Server Configuration Manager in Computer Management

 So according to this doc on MS docs, I should be able to run configuration manager against a remote SQL server from a machine with a GUI.


https://docs.microsoft.com/en-us/sql/database-engine/install-windows/configure-sql-server-on-a-server-core-installation?view=sql-server-ver15


Except, as you can see from the screenshot, "SQL Server Configuration Manager" isn't anywhere in there.





The server is Windows Server Core 2019. MMC is a no-go.

So let's find the .msc, I thought, and so I pointed my File Explorer at c:\windows\system32. So I tried starting it manually, and got the error "Class not registered".



OK, this is Server Core, that's not really all that shocking.

I copied the .msc from my Server Core machine over to the GUI server I have SSMS installed on, and tried running it from there. This is the error I get.





So I'm going to have to figure out how to install the configuration manager without installing SQL Server itself.