Today, while monitoring my data base I see an special session_id with negative number. (-4)
Anyone would help me to understand what do it please?
SQL Server 2008 R2 – Session_id: Negative Number
sql-server-2008-r2t-sql
Related Solutions
I'm not sure how this will scale but it's an answer :)
I should note that I'm summing for CN.RowNum < ClassNumbered.RowNum and not <= for a reason. This way I can return everything lower than the requested value (2.0 in this case). If I did <= then I would have to return everything <= 2.0 which won't work if the three values happen to be .5, 1 and 1 for a total of 2.5. You wouldn't get the last class. I also broke this up by subject so that if you have more than one subject you will get multiple groupings by subject.
WITH ClassNumbered AS (SELECT [Subject], Name, Grade, Credit, Pass,
ROW_NUMBER() OVER (ORDER BY [Subject],Credit,Grade) AS RowNum
FROM Classes),
ClassTotals AS (SELECT [Subject], Name, Grade, Credit, Pass,
(SELECT ISNULL(SUM(Credit),0) FROM ClassNumbered CN
WHERE CN.[Subject] = ClassNumbered.[Subject]
AND CN.RowNum < ClassNumbered.RowNum) AS RunningTotal
FROM ClassNumbered)
SELECT *
FROM ClassTotals
WHERE RunningTotal < 2.0
The problem here seems to me to be largely more of a scoping issue - you are likely having difficulty solving this problem on account of the requirements not being defined well enough. With the description and sample data provided, there are at least three partial solutions, none of which may be applicable to your particular use cases. With the test data set up as follows,
IF NOT EXISTS ( SELECT 1
FROM sys.objects
WHERE name = 'Recipe'
AND type = 'U' )
BEGIN
--DROP TABLE dbo.Recipe;
CREATE TABLE dbo.Recipe
(
Recipe INTEGER NOT NULL,
VersionID INTEGER NOT NULL,
Ingredient VARCHAR( 8 ) NOT NULL,
Percentage DECIMAL( 5, 2 )
);
INSERT INTO dbo.Recipe ( Recipe, VersionID, Ingredient, Percentage )
SELECT 4000, 100, 'Ing_1', 23.0
UNION ALL SELECT 4000, 100, 'Ing_100', 0.1
UNION ALL SELECT 4000, 200, 'Ing_1', 20.0
UNION ALL SELECT 4000, 200, 'Ing_100', 0.7
UNION ALL SELECT 4000, 300, 'Ing_1', 22.3
UNION ALL SELECT 4000, 300, 'Ing_100', 0.9
UNION ALL SELECT 4001, 900, 'Ing_1', 8.3
UNION ALL SELECT 4001, 900, 'Ing_100', 72.4
UNION ALL SELECT 4001, 901, 'Ing_1', 9.3
UNION ALL SELECT 4001, 901, 'Ing_100', 70.5
UNION ALL SELECT 5012, 871, 'Ing_1', 45.1
UNION ALL SELECT 5012, 871, 'Ing_100', 0.9
UNION ALL SELECT 5012, 877, 'Ing_1', 47.2
UNION ALL SELECT 5012, 877, 'Ing_100', 0.8
UNION ALL SELECT 5012, 879, 'Ing_1', 46.6
UNION ALL SELECT 5012, 879, 'Ing_100', 0.9
UNION ALL SELECT 5012, 880, 'Ing_1', 43.6
UNION ALL SELECT 5012, 880, 'Ing_100', 1.2;
ALTER TABLE dbo.Recipe
ADD CONSTRAINT PK__Recipe
PRIMARY KEY CLUSTERED ( Recipe, VersionID, Ingredient );
CREATE NONCLUSTERED INDEX IX__Recipe__Recipe__VersionID
ON dbo.Recipe ( Recipe, VersionID )
INCLUDE ( Percentage );
END;
GO
we can use our new table to explore some possible solutions. Expanding on the sample output, we add the next recipe into the result set to illustrate the difficulty with the question.
RECIPE --- INGREDIENT_V100 --- PERCENTAGE_V100 --- INGREDIENT_V200 --- INGREDIENT_V200
4000 Ing_1 23,0 Ing_1 20,0
4000 Ing_100 0,1 Ing_100 0,7
4001 Ing_1 8,3 Ing_1 9,3
4001 Ing_100 72,4 Ing_100 70,5
Columns %_V100 and %_V200 make perfect sense in the case of the 4000 recipe, but quickly lose their meaning as additional recipes are added. The 4001 recipe would need new and separate columns to properly label the data by version, but since the version numbers differ for each and every recipe, that path leads us to a very sparse result set which would be downright annoying to use, or we must alias the columns, losing the version number data.
Solution 1:
Starting with what I feel is the absolute worst way of going about this, let's look at the sparse result set. For the sample data, we would be attempting to generate a query which would look like something along the following lines:
SELECT p.Recipe,
[Ingredient_v100] = CASE WHEN p.[100] IS NULL THEN NULL ELSE p.[Ingredient] END, [Percentage_v100] = p.[100],
[Ingredient_v200] = CASE WHEN p.[200] IS NULL THEN NULL ELSE p.[Ingredient] END, [Percentage_v200] = p.[200],
[Ingredient_v300] = CASE WHEN p.[300] IS NULL THEN NULL ELSE p.[Ingredient] END, [Percentage_v300] = p.[300],
[Ingredient_v871] = CASE WHEN p.[871] IS NULL THEN NULL ELSE p.[Ingredient] END, [Percentage_v871] = p.[871],
[Ingredient_v877] = CASE WHEN p.[877] IS NULL THEN NULL ELSE p.[Ingredient] END, [Percentage_v877] = p.[877],
[Ingredient_v879] = CASE WHEN p.[879] IS NULL THEN NULL ELSE p.[Ingredient] END, [Percentage_v879] = p.[879],
[Ingredient_v880] = CASE WHEN p.[880] IS NULL THEN NULL ELSE p.[Ingredient] END, [Percentage_v880] = p.[880],
[Ingredient_v900] = CASE WHEN p.[900] IS NULL THEN NULL ELSE p.[Ingredient] END, [Percentage_v900] = p.[900],
[Ingredient_v901] = CASE WHEN p.[901] IS NULL THEN NULL ELSE p.[Ingredient] END, [Percentage_v901] = p.[901]
FROM ( SELECT r.Recipe,
r.VersionID,
r.Ingredient,
r.Percentage
FROM dbo.Recipe r ) s
PIVOT ( MAX( s.Percentage )
FOR s.VersionID IN ( [100], [200], [300], [871], [877], [879], [880], [900], [901] ) ) p
ORDER BY p.Recipe;
On account of the variable number of versions, we can use some dynamic SQL to generate and execute the query.
DECLARE @Piv NVARCHAR( MAX ),
@Col NVARCHAR( MAX ),
@SQL NVARCHAR( MAX );
SELECT @Piv = LEFT( b.Piv, LEN( b.Piv ) - 1 )
FROM ( SELECT N'[' + CONVERT( VARCHAR( 8 ), a.VersionID ) + '], '
FROM ( SELECT DISTINCT r.VersionID
FROM dbo.Recipe r ) a
ORDER BY a.VersionID
FOR XML PATH ( '' ) ) b ( Piv );
SELECT @Col = LEFT( b.Piv, LEN( b.Piv ) - 1 )
FROM ( SELECT N'[Ingredient_v' + CONVERT( VARCHAR( 8 ), a.VersionID ) + '] = CASE'
+ ' WHEN p.[' + CONVERT( VARCHAR( 8 ), a.VersionID ) + '] IS NULL THEN NULL'
+ ' ELSE p.[Ingredient] END, '
+ '[Percentage_v' + CONVERT( VARCHAR( 8 ), a.VersionID ) + '] = p.['
+ CONVERT( VARCHAR( 8 ), a.VersionID ) + '], '
FROM ( SELECT DISTINCT r.VersionID
FROM dbo.Recipe r ) a
ORDER BY a.VersionID
FOR XML PATH ( '' ) ) b ( Piv );
SET @SQL = N'
SELECT p.Recipe, ' + @Col + '
FROM ( SELECT r.Recipe,
r.VersionID,
r.Ingredient,
r.Percentage
FROM dbo.Recipe r ) s
PIVOT ( MAX( s.Percentage )
FOR s.VersionID IN ( ' + @Piv + ' ) ) p
ORDER BY p.Recipe;';
EXECUTE dbo.sp_executesql @statement = @SQL;
GO
This result set plainly sucks. Here's an SQL Fiddle which displays the results, take a peek and let's move on.
Solution 2:
With the sparse result set proving to be next to useless, we can accept losing the version numbers of the recipes and simply order them in ascending version number. For the purpose of the example we'll alias alphabetically, so that versions 100, 200 and 300 of recipe 4000 will receive A, B and C designations, whereas versions 900 and 901 will receive just A and B. The query we would like to generate for this should look somewhat like the following:
SELECT p.Recipe,
[Ingredient_vA] = p.[Ingredient], [Percentage_vA] = ISNULL( p.[Percentage_vA], 0 ),
[Ingredient_vB] = p.[Ingredient], [Percentage_vB] = ISNULL( p.[Percentage_vB], 0 ),
[Ingredient_vC] = p.[Ingredient], [Percentage_vC] = ISNULL( p.[Percentage_vC], 0 ),
[Ingredient_vD] = p.[Ingredient], [Percentage_vD] = ISNULL( p.[Percentage_vD], 0 )
FROM ( SELECT Lvl = 'Percentage_v' + CHAR( 64 +
DENSE_RANK() OVER (
PARTITION BY r.Recipe
ORDER BY r.VersionID ) ),
r.Recipe,
r.Ingredient,
r.Percentage
FROM dbo.Recipe r ) s
PIVOT ( MAX( s.Percentage )
FOR s.Lvl IN ( [Percentage_vA], [Percentage_vB], [Percentage_vC], [Percentage_vD] ) ) p
ORDER BY p.Recipe;
Similarly to solution one, dynamic SQL can be leveraged to accomplish this.
DECLARE @Piv NVARCHAR( MAX ),
@Col NVARCHAR( MAX ),
@SQL NVARCHAR( MAX );
SELECT @Piv = LEFT( b.Piv, LEN( b.Piv ) - 1 )
FROM ( SELECT N'[Percentage_v' + CHAR( 64 + a.Lvl ) + '], '
FROM ( SELECT DISTINCT Lvl = DENSE_RANK()
OVER ( PARTITION BY r.Recipe
ORDER BY r.VersionID )
FROM dbo.Recipe r ) a
ORDER BY a.Lvl
FOR XML PATH ( '' ) ) b ( Piv );
SELECT @Col = LEFT( b.Col, LEN( b.Col ) - 1 )
FROM ( SELECT N'[Ingredient_v' + CHAR( 64 + a.Lvl ) + '] = p.[Ingredient], '
+ '[Percentage_v' + CHAR( 64 + a.Lvl ) + '] = ISNULL( p.[Percentage_v'
+ CHAR( 64 + a.Lvl ) + '], 0 ),'
FROM ( SELECT DISTINCT Lvl = DENSE_RANK()
OVER ( PARTITION BY r.Recipe
ORDER BY r.VersionID )
FROM dbo.Recipe r ) a
ORDER BY a.Lvl
FOR XML PATH ( '' ) ) b ( Col );
SET @SQL = N'
SELECT p.Recipe, ' + @Col + '
FROM ( SELECT Lvl = ''Percentage_v'' + CHAR( 64 +
DENSE_RANK() OVER (
PARTITION BY r.Recipe
ORDER BY r.VersionID ) ),
r.Recipe,
r.Ingredient,
r.Percentage
FROM dbo.Recipe r ) s
PIVOT ( MAX( s.Percentage )
FOR s.Lvl IN ( ' + @Piv + ' ) ) p
ORDER BY p.Recipe;';
EXECUTE dbo.sp_executesql @statement = @SQL;
GO
This ends up in a much prettier result set, as can be seen in this SQL Fiddle, despite the loss of the specific version numbers of each recipe.
Solution 3:
If the loss of the version numbers can not be tolerated, a hybrid approach can be implemented, though the results from each call would be limited to only a single recipe. In effect, our goal SQL would be similar to the first solution, but with an expressly defined Recipe number.
SELECT p.Recipe,
[Ingredient_v100] = CASE WHEN p.[100] IS NULL THEN NULL ELSE p.[Ingredient] END, [Percentage_v100] = p.[100],
[Ingredient_v200] = CASE WHEN p.[200] IS NULL THEN NULL ELSE p.[Ingredient] END, [Percentage_v200] = p.[200],
[Ingredient_v300] = CASE WHEN p.[300] IS NULL THEN NULL ELSE p.[Ingredient] END, [Percentage_v300] = p.[300]
FROM ( SELECT r.Recipe,
r.VersionID,
r.Ingredient,
r.Percentage
FROM dbo.Recipe r
WHERE r.Recipe = @Recipe ) s
PIVOT ( MAX( s.Percentage )
FOR s.VersionID IN ( [100], [200], [300] ) ) p
ORDER BY p.Recipe;
Generation could be handled as follows:
DECLARE @Piv NVARCHAR( MAX ),
@Col NVARCHAR( MAX ),
@Param NVARCHAR( MAX ),
@SQL NVARCHAR( MAX ),
@Recipe INTEGER = 4000;
SELECT @Piv = LEFT( b.Piv, LEN( b.Piv ) - 1 )
FROM ( SELECT N'[' + CONVERT( VARCHAR( 8 ), a.VersionID ) + '], '
FROM ( SELECT DISTINCT r.VersionID
FROM dbo.Recipe r
WHERE Recipe = @Recipe ) a
ORDER BY a.VersionID
FOR XML PATH ( '' ) ) b ( Piv );
SELECT @Col = LEFT( b.Piv, LEN( b.Piv ) - 1 )
FROM ( SELECT N'[Ingredient_v' + CONVERT( VARCHAR( 8 ), a.VersionID ) + '] = CASE'
+ ' WHEN p.[' + CONVERT( VARCHAR( 8 ), a.VersionID ) + '] IS NULL THEN NULL'
+ ' ELSE p.[Ingredient] END, '
+ '[Percentage_v' + CONVERT( VARCHAR( 8 ), a.VersionID ) + '] = p.['
+ CONVERT( VARCHAR( 8 ), a.VersionID ) + '], '
FROM ( SELECT DISTINCT r.VersionID
FROM dbo.Recipe r
WHERE Recipe = @Recipe ) a
ORDER BY a.VersionID
FOR XML PATH ( '' ) ) b ( Piv );
SET @Param = N'@Recipe INTEGER';
SET @SQL = N'
SELECT p.Recipe, ' + @Col + '
FROM ( SELECT r.Recipe,
r.VersionID,
r.Ingredient,
r.Percentage
FROM dbo.Recipe r
WHERE r.Recipe = @Recipe ) s
PIVOT ( MAX( s.Percentage )
FOR s.VersionID IN ( ' + @Piv + ' ) ) p
ORDER BY p.Recipe;';
EXECUTE dbo.sp_executesql @statement = @SQL, @param = @Param, @Recipe = @Recipe;
GO
The results could then be accessed per recipe, as shown in this SQL Fiddle or this one.
Related Question
- SQL Server – Standard Data Types for Email, Phone, Name, and Rich Text
- SQL Server Full Text Search – Slow Performance with Multiple Searches
- SQLcmd Error – Failed to Initialize sqlcmd Library
- Sql-server – High number of page reads and PLE counter, but only when tracing with Profiler
- SQL Server – Resolving SSIS Issues with Temp Table Referencing
- Sql-server – SP_WhoIsActive – RESOURCE_SEMAPHORE_QUERY_COMPILE
- T-sql – TSQL SELECT statement to list a column calculated based on the value of a field
Best Answer
Per BOL:
I did a write up on it here. However the simple way to get rid of it
I wasn't able to find a whole lot of information on -4 spids. Everything I was able to find sounded like a tempdb issue. This is the solution I use to get rid of -2s but it should work for you:
Take each row from the result and use it to create a KILL statement.
Be warned however we do have a system that gets -2s (I know that's different but even so) on a regular basis and if you kill them it can cause problems with the system. If this is the only time you've seen it I think you'll be fine though.