June 12, 2010 at 12:09 am
hello
a problem with SQL server 2005, a procedure that executes 27 seconds, as it is called from another procedure that all 8 CPU jump to 100% and the procedure is in place.
Plans for implementation in both cases are the same. I tried to update stats, DBCC freeproccache, and added a drop indexes.
rk
June 12, 2010 at 2:42 am
Please post query, table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Why add a drop indexes?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 12, 2010 at 6:01 am
procedure is very complex, I enclose a piece that makes trouble,
but unfortunately I can not because it is the whole secret of the company.
index, and added a drop because of this began.
I added an index on that view point to
SELECT
migs.user_seeks * migs.avg_total_user_cost * (migs.avg_user_impact * 0.01) AS index_advantage,
migs.last_user_seek,
mid.[statement] AS [Database.Schema.Table],
mid.equality_columns,
mid.inequality_columns,
mid.included_columns,
migs.unique_compiles,
migs.user_seeks,
migs.avg_total_user_cost,
migs.avg_user_impact
FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)
INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK)
ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK)
ON mig.index_handle = mid.index_handle
WHERE database_id =DB_ID ('database')
ORDER BY index_advantage DESC;
after it is removed, nothing has changed
table has more than 50 million records
June 12, 2010 at 7:03 am
Can you post the definition of the rachunki table, and all indexes on it?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 12, 2010 at 11:24 am
You have an "accidental cross join" (ie... many-to-many) in the query due to either insufficient criteria or improper use of the UPDATE statement. Take a look in the estimated execution plan you provided... it creates a very thick arrow with rowcount of 852,315,876 rows. You need to figure out what join criteria should actually be used and you need to figure out the proper way to use an UPDATE.
I'm picking on the UPDATE because I've seen this behaviour before. The fix is simple but you need to identify the actual cause... check the code that's doing the UPDATE. I believe you'll find that there's either no FROM clause (the UPDATE is all correlated subqueries), the joins do not correctly isolate data (accidental cross join), there's a triangular join in a correlated subquery, OR you do have a FROM clause but the table being updated isn't included in the FROM clause.
That last one is really hard for people to realize what's going on but it has been a major cause of such performance problems. Including the target table in the FROM clause and correctly aliasing it is the simple fix.
In fact... based on the estimated execution plan, I'd say all 4 problems are present. Just fixing 1 may make an improvement but you need to identify and repair all 4 problems in order to keep future problems from rearing their ugly heads.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 13, 2010 at 12:17 am
procedure is not optimal but it gets called one by one in an acceptable time worse than another is called what strange plans for implementation in both cases are the same.
the script is a sensitive part of the procedure
;WITH PULA_NUMEROW (NUMER, RODZAJ_FAKTURY) AS
(
SELECT TOP (@ILOSC4)
'' + SUBSTRING(CAST(@ROK AS VARCHAR(4)), 4, 1) + RIGHT('0' + CAST(@MIESIAC AS VARCHAR(4)), 2) + 'f' +
RIGHT('00000' + CONVERT(VARCHAR(100), ROW_NUMBER() OVER (ORDER BY R.ID_rachunek)), 6) + /*@ABBR*/ '-' NUMER, --PSO 20090813
4 RODZAJ_FAKTURY
FROM dbo.rachunki R WITH (NOLOCK)
UNION
SELECT TOP (@ILOSC8)
'' + SUBSTRING(CAST(@ROK AS VARCHAR(4)), 4, 1) + RIGHT('0' + CAST(@MIESIAC AS VARCHAR(4)), 2) + 'r' +
RIGHT('00000' + CONVERT(VARCHAR(100), ROW_NUMBER() OVER (ORDER BY R.ID_rachunek)), 6) + /*@ABBR*/ '-' NUMER, --PSO 20090813
8 RODZAJ_FAKTURY
FROM dbo.rachunki R WITH (NOLOCK)
)
UPDATE
R
SET
R.Numer_rachunku = TMP.NUMER + RIGHT('000000' + CAST(R.ID_abonent AS VARCHAR(7)), 7)
FROM
dbo.rachunki R WITH (NOLOCK)
JOIN
(
SELECT
ROW_NUMBER() OVER (PARTITION BY R4.Rodzaj_faktury ORDER BY R4.ID_rachunek) ID,
R4.ID_rachunek,
R4.Rodzaj_faktury
FROM
dbo.rachunki R4 WITH (NOLOCK)
WHERE
YEAR(R4.Data_wystawienia) = @ROK
AND MONTH(R4.Data_wystawienia) = @MIESIAC
AND R4.Rodzaj_faktury IN (4, 8)
AND R4.Wydrukowana = 'N'
AND R4.ID_abonent = ISNULL(@ID_ABONENT, R4.ID_abonent)
AND R4.Numer_rachunku NOT LIKE '[0-9][0-9][0-9][rf][0-9][0-9][0-9][0-9][0-9][0-9][-][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' --PSO 20090813
) R3
ON R.ID_rachunek = R3.ID_rachunek
AND R.Rodzaj_faktury = R3.Rodzaj_faktury
JOIN
(
SELECT
ROW_NUMBER() OVER (PARTITION BY PN.RODZAJ_FAKTURY ORDER BY PN.NUMER) ID,
PN.NUMER,
PN.RODZAJ_FAKTURY
FROM
PULA_NUMEROW PN WITH (NOLOCK)
LEFT JOIN dbo.rachunki R2 WITH (NOLOCK)
ON SUBSTRING(R2.Numer_rachunku, 1, 11) = PN.NUMER
AND YEAR(R2.Data_wystawienia) = @ROK
AND MONTH(R2.Data_wystawienia) = @MIESIAC
AND R2.Rodzaj_faktury = PN.RODZAJ_FAKTURY
WHERE
R2.ID_rachunek IS NULL
) TMP
ON TMP.ID = R3.ID
AND TMP.RODZAJ_FAKTURY = R3.Rodzaj_faktury
same table
CREATE TABLE [dbo].[rachunki](
[ID_rachunek] [int] IDENTITY(1,1) NOT NULL,
[Numer_rachunku] [varchar](20) NULL,
[Data_wystawienia] [datetime] NOT NULL,
[Data_platonosci] [datetime] NOT NULL,
[Data_druku] [datetime] NULL
) ON [PRIMARY]
SET ANSI_PADDING OFF
ALTER TABLE [dbo].[rachunki] ADD [Rodzaj_platnosci] [char](1) NOT NULL
ALTER TABLE [dbo].[rachunki] ADD [ID_rachunek_kor] [int] NULL
ALTER TABLE [dbo].[rachunki] ADD [Do_rozliczenia] [money] NOT NULL
ALTER TABLE [dbo].[rachunki] ADD [Data_rozliczenia] [datetime] NULL
ALTER TABLE [dbo].[rachunki] ADD [ID_paczka] [int] NULL
ALTER TABLE [dbo].[rachunki] ADD [Wydrukowana] [char](1) NOT NULL
ALTER TABLE [dbo].[rachunki] ADD [Rodzaj_faktury] [tinyint] NOT NULL
ALTER TABLE [dbo].[rachunki] ADD [ID_abonent] [int] NOT NULL
ALTER TABLE [dbo].[rachunki] ADD [ID_historia_abonenta] [int] NOT NULL
ALTER TABLE [dbo].[rachunki] ADD [ID_firma] [tinyint] NOT NULL
ALTER TABLE [dbo].[rachunki] ADD [ID_historia_firmy] [int] NOT NULL
ALTER TABLE [dbo].[rachunki] ADD [Pozycje] [varchar](255) NULL
ALTER TABLE [dbo].[rachunki] ADD [id_adres_koresp] [int] NULL
ALTER TABLE [dbo].[rachunki] ADD [id_rejestr_typ] [tinyint] NULL DEFAULT (0)
ALTER TABLE [dbo].[rachunki] ADD [przedplata] [int] NULL CONSTRAINT [DF_rachunki_przedplata] DEFAULT (0)
ALTER TABLE [dbo].[rachunki] ADD [faktura_dla_banku] [tinyint] NULL
ALTER TABLE [dbo].[rachunki] ADD [id_bank_historia] [int] NULL
ALTER TABLE [dbo].[rachunki] ADD CONSTRAINT [PKC_Rachunki] PRIMARY KEY CLUSTERED
(
[ID_rachunek] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[rachunki] WITH NOCHECK ADD CONSTRAINT [FKC_R_Abonenci] FOREIGN KEY([ID_abonent])
REFERENCES [dbo].[Abonenci] ([ID_abonent])
GO
ALTER TABLE [dbo].[rachunki] CHECK CONSTRAINT [FKC_R_Abonenci]
GO
ALTER TABLE [dbo].[rachunki] WITH CHECK ADD CONSTRAINT [FKC_r_adresy_koresp] FOREIGN KEY([id_adres_koresp])
REFERENCES [dbo].[Adresy_koresp] ([id_adres_koresp])
GO
ALTER TABLE [dbo].[rachunki] CHECK CONSTRAINT [FKC_r_adresy_koresp]
GO
ALTER TABLE [dbo].[rachunki] WITH NOCHECK ADD CONSTRAINT [FKC_R_Firmy] FOREIGN KEY([ID_firma])
REFERENCES [dbo].[Firmy] ([ID_firma])
GO
ALTER TABLE [dbo].[rachunki] CHECK CONSTRAINT [FKC_R_Firmy]
GO
ALTER TABLE [dbo].[rachunki] WITH CHECK ADD CONSTRAINT [FKC_r_Historia_Abonenta] FOREIGN KEY([ID_historia_abonenta])
REFERENCES [dbo].[Historia_abonenta] ([ID_historia_abonenta])
GO
ALTER TABLE [dbo].[rachunki] CHECK CONSTRAINT [FKC_r_Historia_Abonenta]
GO
ALTER TABLE [dbo].[rachunki] WITH CHECK ADD CONSTRAINT [FKC_r_Historia_Firmy] FOREIGN KEY([ID_historia_firmy])
REFERENCES [dbo].[Historia_firmy] ([ID_historia_firmy])
GO
ALTER TABLE [dbo].[rachunki] CHECK CONSTRAINT [FKC_r_Historia_Firmy]
GO
USE [Andromeda]
GO
CREATE NONCLUSTERED INDEX [_dta_index_rachunki_12_206623779__K12_K11_K1_2_3_13] ON [dbo].[rachunki]
(
[Rodzaj_faktury] ASC,
[Wydrukowana] ASC,
[ID_rachunek] ASC
)
INCLUDE ( [Numer_rachunku],
[Data_wystawienia],
[ID_abonent]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
USE [Andromeda]
GO
CREATE NONCLUSTERED INDEX [index_rachunki_numerowanie] ON [dbo].[rachunki]
(
[Rodzaj_faktury] ASC,
[Numer_rachunku] ASC,
[ID_rachunek] ASC
)
INCLUDE ( [Data_wystawienia]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
June 13, 2010 at 10:51 am
The UPDATE appears to be properly formed in that the target of the update is in the FROM clause so no problem there. I can't really tell because I don't have the data, etc, to be able to actually do some testing, but one of those joins between derived tables/cte is causing an accidental cross join which generates almost a billion internal rows to do the join.
If I had to make an educated guess by looking at the previously supplied execution plan, I'd have to say that the following part of your code is the root cause the accidental cross join...
FROM
PULA_NUMEROW PN WITH (NOLOCK)
LEFT JOIN dbo.rachunki R2 WITH (NOLOCK)
ON SUBSTRING(R2.Numer_rachunku, 1, 11) = PN.NUMER
AND YEAR(R2.Data_wystawienia) = @ROK
AND MONTH(R2.Data_wystawienia) = @MIESIAC
AND R2.Rodzaj_faktury = PN.RODZAJ_FAKTURY
That sub query (I just included the FROM clause) just doesn't appear to have enough uniqueness to it to do anything other than a many-to-many relationship and then that get's joined with yet another (TMP) sub query which causes the internal row count to explode to nearly a billion rows.
Indexing isn't the final solution for this... you need to determine the actual cause of the "accidental cross join" and fix it. As a side bar, you may want to consider breaking this query up. The use of just one very well placed temp table to hold a set of interim results can make things like this run in seconds (or less) instead of hours.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 13, 2010 at 11:04 am
Ah.... another possibly very serious problem... you're updating the Numer_rachunku column. That column is used in joins in two of the sub queries including the one that I think the main problem is in. I believe that what's happening (it doesn't always happen this way, it depends) is that (maybe) the query is behaving as if the entire query is executed once for each row that you update. Compound that along with the possible accidental cross join I think the code has and it's easy to understand why it generates a billion internal working rows.
My recommendation would be to gather and store the updates and enough key information in a temp table and then do a final separate update.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply