SSMS 2012 data issue. RAM problem? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

SSMS 2012 data issue. RAM problem?

Author

Topic

kotonikak
Yak Posting Veteran

92 Posts

Posted - 2013-06-07 : 12:33:23

Hello. I'm pretty new when it comes to memory issues with SQL. Our company currently has SSMS 2012 (we updated from SSMS 2008 R2).

I'm running a procedure in SSMS 2012 that has a cursor. Within the cursor, I have a dynamic SQL statement that is grabbing certain data from tables. When the procedure is done, I notice that the data grabbed from those tables is correct in certain instances and incorrect in others. This changes with every run (final result is VERY large). I know my code is correct because I have tested it with less data and my coworker has checked it as well.

I did just notice that the 2008 R2 version has recommended RAM of 2.048GB or more and the 2012 version has recommended RAM of at least 4GB. Our company currently has 4GB of RAM on our server (blame IT). Could this be a reason as to why when I run it one time I get correct data and when I run it another time I don't?

Any sort of explanation would be helpful as I am pretty new to this stuff.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-06-07 : 13:11:12

highly doubtful.
I'm going to take a wild guess and say that your logic depends on the sequence of whatever work is being performed in your code. And that something (maybe parallelism of multiple processors) is causing your data to be processed in a sequence you didn't anticipate.

Of course I don't know what your code does but very often developers think that a procedural based process (like using a cursor) is necessary when it really isn't. Can you post the code and let us take a look?

Be One with the Optimizer
TG

kotonikak
Yak Posting Veteran

92 Posts

Posted - 2013-06-07 : 14:03:37

Assume table is created and all the parameters are defined.
Code goes through 2 cursors. It grabs the RptLOB (46 of them) and then the PerilRegion (23 of them). Combination of 1058.

DECLARE c2 CURSOR local READ_ONLY FOR
select RptLOB
from CapModel.RptLineOfBusiness
order by RptLOBPrintOrder

OPEN c2

FETCH NEXT FROM c2 INTO @LOB

WHILE @@FETCH_STATUS = 0
BEGIN

if @LOB != 'NoLOB'
begin

set @ModelType = (select ModelType from CapModel.AdviseModelType where RptLOB = @LOB)
if @ModelType in ('Aggregate and Catastrophe Models','Aggregate, Individual and Catastrophe Models','Individual and Catastrophe Models')
begin

DECLARE c3 CURSOR global READ_ONLY FOR
select PerilRegion
from dbo.PerilRegionCombinationList
where ModelInAdvise = 'Yes'
order by PerilRegion

OPEN c3

FETCH NEXT FROM c3 INTO @PerilRegion

WHILE @@FETCH_STATUS = 0
BEGIN

set @Peril = LEFT(@PerilRegion, 2)
set @Region = RIGHT(@PerilRegion, LEN(@PerilRegion)-3)

--print @Peril+' '+@Region

-- Base Exposure Units
set @GEP = (select sum(Amount) as AnnualGEP from CapModel.LedgerAmountByECMAccountByRptLOB where AccountingDate=@AcctDate and Period in ('2013Q1','2013Q2','2013Q3','2013Q4') and RptLOB=@LOB and ECMAccount='Gross Earned Premium' group by RptLOB)
insert into AnalyticsV2P7.dbo.ResultCSVCat values('Generic','','','','','','','','','')
insert into AnalyticsV2P7.dbo.ResultCSVCat values('Company Inputs','Insurance','Products',@LOB,'"Future Losses, ALAE and SS"','"Catastrophe Losses, ALAE and SS"','Perils',@PerilRegion,'Base Exposure Units','')
insert into AnalyticsV2P7.dbo.ResultCSVCat values(@GEP,'','','','','','','','','')
insert into AnalyticsV2P7.dbo.ResultCSVCat values('','','','','','','','','','')

-- Multiply losses by two times the intra-period timing of the event
insert into AnalyticsV2P7.dbo.ResultCSVCat values('Generic','','','','','','','','','')
insert into AnalyticsV2P7.dbo.ResultCSVCat values('Company Inputs','Insurance','Products',@LOB,'"Future Losses, ALAE and SS"','"Catastrophe Losses, ALAE and SS"','Perils',@PerilRegion,'Multiply losses by two times the intra-period timing of the event','')
insert into AnalyticsV2P7.dbo.ResultCSVCat values(0,'','','','','','','','','')
insert into AnalyticsV2P7.dbo.ResultCSVCat values('','','','','','','','','','')

-- Nominal or Real?
insert into AnalyticsV2P7.dbo.ResultCSVCat values('Generic','','','','','','','','','')
insert into AnalyticsV2P7.dbo.ResultCSVCat values('Company Inputs','Insurance','Products',@LOB,'"Future Losses, ALAE and SS"','"Catastrophe Losses, ALAE and SS"','Perils',@PerilRegion,'Nominal or Real?','')
insert into AnalyticsV2P7.dbo.ResultCSVCat values('Nominal','','','','','','','','','')
insert into AnalyticsV2P7.dbo.ResultCSVCat values('','','','','','','','','','')

-- Multiplicative Modifier
insert into AnalyticsV2P7.dbo.ResultCSVCat values('Generic','','','','','','','','','')
insert into AnalyticsV2P7.dbo.ResultCSVCat values('Company Inputs','Insurance','Products',@LOB,'"Future Losses, ALAE and SS"','"Catastrophe Losses, ALAE and SS"','Perils',@PerilRegion,'Multiplicative Modifier','')
insert into AnalyticsV2P7.dbo.ResultCSVCat values('None','','','','','','','','','')
insert into AnalyticsV2P7.dbo.ResultCSVCat values('','','','','','','','','','')

-- Model Choice and Event Tables
insert into AnalyticsV2P7.dbo.ResultCSVCat values('Generic','','','','','','','','','')
insert into AnalyticsV2P7.dbo.ResultCSVCat values('Company Inputs','Insurance','Products',@LOB,'"Future Losses, ALAE and SS"','"Catastrophe Losses, ALAE and SS"','Perils',@PerilRegion,'Event Table','Model Choice')

if exists(SELECT name FROM [Catastrophe 12Q4].sys.columns WHERE name LIKE '%Cat'+@Peril+'_'+@LOB+'_'+@Region+'_Hld%' AND object_id IN (object_id('[Catastrophe 12Q4].dbo.ELT_EQ_NET_Consolidated_20130101_Advise'),object_id('[Catastrophe 12Q4].dbo.ELT_WS_withEGYOffshore_Net_Consolidated_20130101_Advise'),object_id('[Catastrophe 12Q4].dbo.ELT_FL_NET_Consolidated_20130101_Advise'),object_id('[Catastrophe 12Q4].dbo.ELT_WT_NET_Consolidated_20130101_Advise'),object_id('[Catastrophe 12Q4].dbo.ELT_CS_NET_Consolidated_20130101_Advise')))
begin

set @CatTableName = (select top 1 object_name(object_id, db_id('Catastrophe 12Q4')) FROM [Catastrophe 12Q4].sys.columns
WHERE name LIKE '%Cat'+@Peril+'_'+@LOB+'_'+@Region+'_'+'Hld%' AND object_id IN (object_id('[Catastrophe 12Q4].dbo.ELT_EQ_NET_Consolidated_20130101_Advise'),object_id('[Catastrophe 12Q4].dbo.ELT_WS_withEGYOffshore_Net_Consolidated_20130101_Advise'),object_id('[Catastrophe 12Q4].dbo.ELT_FL_NET_Consolidated_20130101_Advise'),object_id('[Catastrophe 12Q4].dbo.ELT_WT_NET_Consolidated_20130101_Advise'),object_id('[Catastrophe 12Q4].dbo.ELT_CS_NET_Consolidated_20130101_Advise')))

set @LossColumn = '['+(select top 1 name FROM [Catastrophe 12Q4].sys.columns
WHERE name LIKE '%Cat'+@Peril+'_'+@LOB+'_'+@Region+'_'+'Hld%' AND object_id IN (object_id('[Catastrophe 12Q4].dbo.ELT_EQ_NET_Consolidated_20130101_Advise'),object_id('[Catastrophe 12Q4].dbo.ELT_WS_withEGYOffshore_Net_Consolidated_20130101_Advise'),object_id('[Catastrophe 12Q4].dbo.ELT_FL_NET_Consolidated_20130101_Advise'),object_id('[Catastrophe 12Q4].dbo.ELT_WT_NET_Consolidated_20130101_Advise'),object_id('[Catastrophe 12Q4].dbo.ELT_CS_NET_Consolidated_20130101_Advise')))+']'

set @ExpColumn = '[' + (select top 1 name FROM [Catastrophe 12Q4].sys.columns
WHERE name LIKE '%Cat'+@Peril+'_'+@LOB+'_'+@Region+'_'+'Hld_EXPVALUE%' AND object_id IN (object_id('[Catastrophe 12Q4].dbo.ELT_EQ_NET_Consolidated_20130101_Advise'),object_id('[Catastrophe 12Q4].dbo.ELT_WS_withEGYOffshore_Net_Consolidated_20130101_Advise'),object_id('[Catastrophe 12Q4].dbo.ELT_FL_NET_Consolidated_20130101_Advise'),object_id('[Catastrophe 12Q4].dbo.ELT_WT_NET_Consolidated_20130101_Advise'),object_id('[Catastrophe 12Q4].dbo.ELT_CS_NET_Consolidated_20130101_Advise')))+']'

set @StdDevIndep = '[' + (select top 1 name FROM [Catastrophe 12Q4].sys.columns
WHERE name LIKE '%Cat'+@Peril+'_'+@LOB+'_'+@Region+'_'+'Hld_STDDEVI%' AND object_id IN (object_id('[Catastrophe 12Q4].dbo.ELT_EQ_NET_Consolidated_20130101_Advise'),object_id('[Catastrophe 12Q4].dbo.ELT_WS_withEGYOffshore_Net_Consolidated_20130101_Advise'),object_id('[Catastrophe 12Q4].dbo.ELT_FL_NET_Consolidated_20130101_Advise'),object_id('[Catastrophe 12Q4].dbo.ELT_WT_NET_Consolidated_20130101_Advise'),object_id('[Catastrophe 12Q4].dbo.ELT_CS_NET_Consolidated_20130101_Advise')))+']'

set @StdDevCorr = '[' + (select top 1 name FROM [Catastrophe 12Q4].sys.columns
WHERE name LIKE '%Cat'+@Peril+'_'+@LOB+'_'+@Region+'_'+'Hld_STDDEVC%' AND object_id IN (object_id('[Catastrophe 12Q4].dbo.ELT_EQ_NET_Consolidated_20130101_Advise'),object_id('[Catastrophe 12Q4].dbo.ELT_WS_withEGYOffshore_Net_Consolidated_20130101_Advise'),object_id('[Catastrophe 12Q4].dbo.ELT_FL_NET_Consolidated_20130101_Advise'),object_id('[Catastrophe 12Q4].dbo.ELT_WT_NET_Consolidated_20130101_Advise'),object_id('[Catastrophe 12Q4].dbo.ELT_CS_NET_Consolidated_20130101_Advise')))+']'

set @ModelChoice = 'Defined Events with Secondary Uncertainty'
insert into AnalyticsV2P7.dbo.ResultCSVCat values(@ModelChoice,'','','','','','','','','')
insert into AnalyticsV2P7.dbo.ResultCSVCat values('','','','','','','','','','')

insert into AnalyticsV2P7.dbo.ResultCSVCat values('Generic','','','','','','','','','')
insert into AnalyticsV2P7.dbo.ResultCSVCat values('Company Inputs','Insurance','Products',@LOB,'"Future Losses, ALAE and SS"','"Catastrophe Losses, ALAE and SS"','Perils',@PerilRegion,'Event Table',@ModelChoice)
insert into AnalyticsV2P7.dbo.ResultCSVCat values('Event ID','Poisson frequency','Loss and ALAE','Exposure','Standard Deviation (Independent)','Standard Deviation (Correlated)','','','','')

set @SQL = N'select EVENTID, cast(RATE as decimal(30,30)) as RATE,' + @LossColumn + N',' + @ExpColumn + N',' + @StdDevIndep + N',' + @StdDevCorr + N', '''','''', '''',''''
from [Catastrophe 12Q4].dbo.' + @CatTableName + N' where ' + @ExpColumn + N'>0 and ' + @LossColumn + N'>0 and ' + @StdDevCorr + '+' + @StdDevIndep + N'>0
and ((1-'+@LossColumn+N'/'+@ExpColumn+N')/((('+@StdDevIndep+N'+'+@StdDevCorr+N')/'+@LossColumn+N')*(('+@StdDevIndep+N'+'+@StdDevCorr+N')/'+@LossColumn+N'))-('+@LossColumn+N'/'+@ExpColumn+N'))>0
and (((1-'+@LossColumn+N'/'+@ExpColumn+N')/((('+@StdDevIndep+N'+'+@StdDevCorr+N')/'+@LossColumn+N')*(('+@StdDevIndep+N'+'+@StdDevCorr+N')/'+@LossColumn+N'))-('+@LossColumn+N'/'+@ExpColumn+N'))*(1-'+@LossColumn+N'/'+@ExpColumn+N')/('+@LossColumn+N'/'+@ExpColumn+'))>0'

insert into AnalyticsV2P7.dbo.ResultCSVCat
execute sp_executesql @SQL

insert into AnalyticsV2P7.dbo.ResultCSVCat values('','','','','','','','','','')

end
else
begin
set @ModelChoice = 'None'
insert into AnalyticsV2P7.dbo.ResultCSVCat values(@ModelChoice,'','','','','','','','','')
insert into AnalyticsV2P7.dbo.ResultCSVCat values('','','','','','','','','','')
end

FETCH NEXT FROM c3 INTO @PerilRegion;
END
CLOSE c3
DEALLOCATE c3

end

end --if @LOB != 'NoLOB'

FETCH NEXT FROM c2 INTO @LOB;
END -- WHILE @@FETCH_STATUS = 0 for c2
CLOSE c2
DEALLOCATE c2

xp_cmdshell 'sqlcmd -S (local) -d AnalyticsV2P7 -E -s, -W -h-1 -i "E:\ExportToCSVCat.sql" -o "E:\Test1.csv" '

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-06-07 : 16:29:09

The statements that "select top 1..." from syscolumns don't have an explicit ORDER BY. So every time you run it you could potentially get a different answer which would resolve to different exec'd statements. That is the only thing I can see that will give you different results given no changes to the underlying data. It is definitely not a memory thing.

Be One with the Optimizer
TG

kotonikak
Yak Posting Veteran

92 Posts

Posted - 2013-06-07 : 16:50:05

Hm thanks for looking at that. I'll definitely need to change it and see if that happens again!

kotonikak
Yak Posting Veteran

92 Posts

Posted - 2013-06-11 : 09:29:45

Although I did change what TG said, I'm still getting weird errors. When a table is being inserted, sometimes there is an empty row randomly (and there are no empty rows in any of my tables). This has happened in the past and I can't explain why it's happening. I'm not sure if it's the mere size of it or something else.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-06-11 : 09:49:59

Well I do see several places including an ELSE block that specifies an "empty row":
insert into AnalyticsV2P7.dbo.ResultCSVCat values('','','','','','','','','','')

regarding the "randomly" comment - is there an identity column on this target table? What controls the order when you select out of this table?

for trouble shooting you might try one of the following:

- print out the current cursor values and each resolved insert statement in your code so that you can identify any unexpected conditions and for which specific values by looking at the output.

- add some new columns in your table to insert the current cursor values so you can see which iteration(s) are responsible for the unexpected data. Then run the statements manually for just those values to see why you are getting the unexpected results.

Be One with the Optimizer
TG

kotonikak
Yak Posting Veteran

92 Posts

Posted - 2013-06-11 : 12:42:22

I do have a lot of insert statements depicting empty rows. These are always after certain blocks. So I'll have my dynamic sql statement inserting a specific table I want and then inserting the empty row so it can move on to the next block. There may be an empty row in the middle of my table (which is causing the issue) but it's also after my table as it should be. This is not for a specific cursor value, it happens in different ones when I run it (and it doesn't always insert an empty row every time). I'll look at some of your suggestions to see if those fix any bugs.

kotonikak
Yak Posting Veteran

92 Posts

Posted - 2013-06-11 : 12:49:53

I don't know if this helps but we never had this issue with SSMS 2008 R2.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-06-11 : 13:48:48

quote:
There may be an empty row in the middle of my table (which is causing the issue) but it's also after my table as it should be.

Still curious about the ordering. I don't see anything you are doing to control the order. Perhaps these "extra blank rows" are ones that you expect to be somewhere else. How are you viewing this data? Do you use a SELECT with an ORDER BY clause? If so what are you ordering by? without an ORDER BY clause you can't expect the data to appear in any consistent order. And if you don't use an order by clause it is likely that different hardware and different sql versions will return the same results in different orders.

EDIT:
The ordering thing jives with what you said about the results being "ok" when you used a small data set. When sql server has a lot of work to do it can launch multiple processes in parallel and then combine the results. When that happens without an ORDER BY clause you will definitely get data back in different orders.

I'm feeling

Be One with the Optimizer
TG

kotonikak
Yak Posting Veteran

92 Posts

Posted - 2013-06-11 : 14:34:10

Are you talking about the ordering in my dynamic sql (order of the output table shouldn't matter) or the ordering of the whole code itself? The cursors have an order by statement. I can't order the final result as you can see below. This is how my output looks like (very small part of it):

Generic
Company Inputs Insurance Products DIC Future Losses, ALAE and SS Catastrophe Losses, ALAE and SS Perils CS AU Base Exposure Units
11447809.68

Generic
Company Inputs Insurance Products DIC Future Losses, ALAE and SS Catastrophe Losses, ALAE and SS Perils CS AU Multiply losses by two times the intra-period timing of the event
0

Generic
Company Inputs Insurance Products DIC Future Losses, ALAE and SS Catastrophe Losses, ALAE and SS Perils CS AU Nominal or Real?
Nominal

Generic
Company Inputs Insurance Products DIC Future Losses, ALAE and SS Catastrophe Losses, ALAE and SS Perils CS AU Multiplicative Modifier
None

Generic
Company Inputs Insurance Products DIC Future Losses, ALAE and SS Catastrophe Losses, ALAE and SS Perils CS AU Event Table Model Choice
None

Generic
Company Inputs Insurance Products AE Future Losses, ALAE and SS Catastrophe Losses, ALAE and SS Perils CS US x GOM Base Exposure Units
11447809.68

Generic
Company Inputs Insurance Products AE Future Losses, ALAE and SS Catastrophe Losses, ALAE and SS Perils CS US x GOM Multiply losses by two times the intra-period timing of the event
0

Generic
Company Inputs Insurance Products AE Future Losses, ALAE and SS Catastrophe Losses, ALAE and SS Perils CS US x GOM Nominal or Real?
Nominal

Generic
Company Inputs Insurance Products AE Future Losses, ALAE and SS Catastrophe Losses, ALAE and SS Perils CS US x GOM Multiplicative Modifier
None

Generic
Company Inputs Insurance Products AE Future Losses, ALAE and SS Catastrophe Losses, ALAE and SS Perils CS US x GOM Event Table Model Choice
None

....where DIC and AE are part of the first cursor and "CS AU" and "CS US x GOM" are part of the 2nd cursor.

When I view this I can't order by anything. As you can see, it's not a table where I can do that. Some values for the first cursor don't have any tables to show for the dynamic sql, there's only certain lines like below that actually have numeric outputs:

Generic
Company Inputs Insurance Products DIC Future Losses, ALAE and SS Catastrophe Losses, ALAE and SS Perils EQ US x GOM Base Exposure Units
11447809.68

Generic
Company Inputs Insurance Products DIC Future Losses, ALAE and SS Catastrophe Losses, ALAE and SS Perils EQ US x GOM Multiply losses by two times the intra-period timing of the event
0

Generic
Company Inputs Insurance Products DIC Future Losses, ALAE and SS Catastrophe Losses, ALAE and SS Perils EQ US x GOM Nominal or Real?
Nominal

Generic
Company Inputs Insurance Products DIC Future Losses, ALAE and SS Catastrophe Losses, ALAE and SS Perils EQ US x GOM Multiplicative Modifier
None

Generic
Company Inputs Insurance Products DIC Future Losses, ALAE and SS Catastrophe Losses, ALAE and SS Perils EQ US x GOM Event Table Model Choice
Defined Events with Secondary Uncertainty

Generic
Company Inputs Insurance Products DIC Future Losses, ALAE and SS Catastrophe Losses, ALAE and SS Perils EQ US x GOM Event Table Defined Events with Secondary Uncertainty
Event ID Poisson frequency Loss and ALAE Exposure Standard Deviation (Independent) Standard Deviation (Correlated)
2006046 2.25E-05 4.21E+07 1.70E+09 1.17E+07 1.84E+07
2006047 2.50E-05 3.46E+07 1.59E+09 1.04E+07 1.53E+07
2006048 5.07E-07 1.15E+08 1.81E+09 2.24E+07 6.95E+07
2006049 1.19E-06 1.06E+08 1.80E+09 2.12E+07 6.44E+07

...and when there's actual values is where i may get a random blank row in between those numeric rows.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-06-11 : 15:37:06

The "order" I am talking about is the order of your output.

quote:
I can't order the final result as you can see below

I think that is the problem. If you want the rows to display in the same order that you inserted them then you could add an identity column to [AnalyticsV2P7].[dbo].[ResultCSVCat] and order by that.

EDIT:
I mean order by the identity column when you select out the results to display.

Be One with the Optimizer
TG

kotonikak
Yak Posting Veteran

92 Posts

Posted - 2013-06-11 : 15:38:51

Will have to try that and let you know! Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources