DB Engine :: Int -> Bigint And Resource Allocations

Jun 8, 2015

I've got a problem: in few months from now my IDs (int) will reach max positive value. The only viable solution is to convert int-s to bigint-s.

There is a story behind this but I believe it's irrelevant. Simply, I need to 'upgrade' int-s to bigint-s; let's take it as a fact.

Question is: is there a formula to calculate increase in disk and RAM consumption? If we presume I'd need to have same number of pages in buffer cache, how can I calculate how much more memory I need?

Just to ease calculation, let's suppose that I have single table with 1e6 rows and 3 columns:

ID (int), PARENT_ID (int) and NAME (NVARCHAR(30)). 

PK is on ID (let's call it PK_ID) and there are 2 indexes on PARENT_ID (i.e. IX_PARENT) and NAME (IX_NAME).

Now, if I turn int-s into bigint-s and still want to keep entire table in cache, how I can calculate how much memory I'd need?

View 5 Replies


DB Engine :: How To Get Resource Consumption

Sep 29, 2015

I got a task to get resource consumption (CPU, storage etc.) for stored procedures in certain database. I found nice script ([dbo].[usp_Worst_TSQL]) but looks like script written for SQL 2005 and we use SQL 2012.how to get resource consumption details.

View 9 Replies View Related

DB Engine :: Resource Governor Classifier Function Script

Aug 7, 2015

I want to create a classifier function where I can restrict all the logins except two logins for the resource pool.

Case Scenario is : I am creating a resource pool and on that pool I want to restrict the users for that particular pool but I want to allow some of the user IDs full access to that databses so how could I create a script for that scenario.

View 4 Replies View Related

DB Engine :: Insufficient System Memory In Resource Pool Default To Run Query

Jul 7, 2015

My Integrity job started failing recently with the following error. Msg 701, Level 17, State 123, Line 1  There is insufficient system memory in resource pool 'default' to run this query.  Process Exit Code 1.  The step failed.

select @@ version
Microsoft SQL Server 2008 R2 (SP2) - 10.50.4033.0 (X64)
 Jul  9 2014 16:04:25
 Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
System is having 4GB RAM and SQL is using most of it. It has 2 processors.

View 8 Replies View Related

High Concurrency, Memory Allocations, And AppDomains

May 2, 2006



How can I keep my thread alive after an out of memory exception? That is, I understand that sometimes a server may be unable to satisfy a memory request, but I'm okay with waiting -- I'm not okay with being terminated (think of the reaction to Oliver asking for some more). I would think that, in general, when any application makes a request for a resource that is currently unavailble, but may be available at another time, that application (process/thread/fiber) would be put in a Wait Queue for that resource. On a high concurrency system, this could obviously lead to deadlocks; however, I think in the situation I describe below, the killing is overkill.

Discussion & Background:

In my project, I have a SqlFunction, we'll call "SqlDecimal BigFunction()" that will allocate a large chunk of memory (~3MB) and can take anywhere from 20ms to 500ms to complete (on my system, assuming no other processor load). There are also Functions that are used to set control points for BigFunction (implying thread/fiber state -- or, if there is a distinction, Transaction state), which we will call "SqlBoolean SetControlPoint(SqlInt32 x, SqlInt32 y)". The 3MB requirement is constant, regardless of the number of control points. (Incidentally, the actual implementations of these functions are in a referenced assembly)

In Code:


[ThreadStatic] // UNSAFE

static externalAssembly.MyClass myObj;


SqlDecimal BigFunction()


if(myObj == null) return -1;

// DoSomeWork will do something like, byte[] b = new byte[3 * 1024 * 1024];

return externalAssembly.DoSomeWork(myObject)



SqlBoolean SetControlPoint(SqlInt32 x, SqlInt32 y)


if(myObj == null) myObj = new externalAssembly.MyClass();

myObj.SetPoint(x.Value, y.Value);

return SqlBoolean.True; // because we can't have 'void' return type



In low to moderate concurrency (single hyperthreaded CPU with 20 sessions banging it in a loop), it *usually* does okay. In a higher concurrency situation (2 hyperthreaded cpus with 10 sessions stressing this code and 10 other sessions doing regular TSQL Selects) It runs for a long time, but will occasionally throw an out-of-memory exception. (Previously, I was managing my thread state manually with a locked dictionary, an Int32 key, and CreateSession/ReleaseSession calls). When an out of memory exception is thrown while the dictionary is locked, I get an AppDomain unload, which is *completely* unacceptable)

So, I know that sometimes, I won't be able to allocate my 3MB (it could be 3kb, it just shows up more readily with a larger allocation request). That doesn't mean my externalAssembly is "misbehaving" or "off-in-the-weeds". It just means the server is loaded right now and can't satisfy my request. One may catch an OutOfMemory Exception (perhaps to add additional info about the point of failure), but the thread is already being aborted.

I tried modifying this implementation to use a buffer pool that is allocated on start-up. That worked pretty well (reduced % Time in GC a bit, also), but it forced my external assembly to be marked as unsafe rather than just external access because of the Synchronization methods used to manage the buffer pool. It also doesn't scale, at least not as it sits. Its just a fixed size buffer pool. With more processors and less peripheral loading, the extra processors would just be waiting for a buffer. Besides that, I thought there was some escalation policy about "waiting too long", but I may be wrong.

I would like to eliminate the "UNSAFE" attribute from the primary assembly -- mainly because it "sounds scary", but more realistically, because it is unsafe! Or at least, experience in the field points to synchronization issues being a primary cause of unreliability in systems. Also, calling the C# lock, Mutex, Monitor etc call into native code to use the OS for locking. When this happens, SQL doesn't really know what you're waiting for and can't take that info into account when scheduling. All it knows is that you're waiting on an OS lock. I thought the hosting API would've allowed the host to optionally implement its own locking primitives, especially a host that runs its own scheduler.

I've looked into constrained execution regions and Chris Brumme's blog entry on hosting. Using them would help ensure some protection, but I think even they do not protect a thread from being unloaded in the face of an OutOfMemoryException (or any asynch exception); rather, they allow you to safely clean up unmanaged references and ensure state integrity for the appdomain.

At any rate, this is getting a little long winded. If anyone has any feedback, I'd be delighted to hear it.

Thank you.


System Info:

SELECT @@version

Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86) Apr 14 2006 01:12:25 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

View 5 Replies View Related

To Bigint, Or Not To Bigint?

May 27, 2008

 Hey, quick question here. In my database for my webapp (The one I showed you guys in my previous post), in my stickies table, I am using a column called StickyId for the PK, and it is of type int. My concern is that eventually, I may get to a point with more then 2 million records in the stickies field. I am looking at using the bigint datatype for StickyId instead, but it takes up twice the memory. So I have two questions:1. Can I declare the StickyId column as type int for now, and if my DB ever starts approaching the 2 million mark, access my DB and change it over to a bigint type? Aka, will MS SQL convert the exisiting int values into a bigint and be transparent to the app and the users?2. Does the bigint always use 8 bytes to store its value? Aka does it pad, so that if I stored the value of '1' would that take the same space as if I stored the value '43563636'? Or is it more like varchar, only taking up the space that that particular value needs?Thanks in advance guys!

View 3 Replies View Related


Mar 18, 2008

Hi there! My question is: I use a bigint data type as a primary key in my database, so
1. Can I define bigint autoincrement as unsigned?
2. In case of possible, what type should I use in C# code that equal to bigint unsigned - ulong?
3. In case of impossible, what type should I use in C# code - long?

View 7 Replies View Related

Bigint Field Vs Int

Sep 23, 2007

i am creating an application which the number of records will be beyond 100000 so i need to set the primary keys to bigint instead of int
i need to know about the performance and difference
will it affect the performance much and second will i have to change any code like delete,update

View 3 Replies View Related


Apr 11, 2006

Hi guys

View 19 Replies View Related

Bigint Datatype

Mar 10, 2008

I am using bigint as a primary key. What happens when bigint reaches the maximum number that it can hold?

The Yak Village Idiot

View 8 Replies View Related

Varchar To Bigint

Jan 2, 2008

I want to write the following query.

SELECT CASE WHEN Member.CuApplicationDocFK = 'NULL' THEN 'Existing' ELSE 'New' END AS MemberType
FROM Member

But the problem is Member.CuApplicationDocFK is bigint type. When I run the query it gives a error message saying "error converting data type varchar to bigint.

Can anyone tell me how to fix this please?


View 5 Replies View Related

Bigint As Unsigned Value?

Oct 4, 2006

The documentation seems to suggest that I can store either a signed or unsigned value in bigint. If I want to store an unsigned value how do I go about it?

View 2 Replies View Related

Convert To BigInt

May 20, 2008

This select qry runs for abt 20 mins, I think convert to bigint is causing the slowness. Underlying tbl has abt 50000 recs. Pl note that I am runnign this in SQL SERVER 2005, Where it takes abt 20 mins. However when I run this on SQL Server 2000, it takes only 4 mins.

ANy ideas to speed up this qry.

select distinct convert(bigint,c.loannum) as loannum ,c.ampsstatus ,((convert(char(10),c.insdate,110) )) as MaxInsdate
from Conversion_AllStatus_History2007 c
where ((convert(char(10),c.insdate,110) )) =(select max((convert(char(10),a.insdate,110) )) from
Conversion_AllStatus_History2007 a
where convert(bigint,a.loannum)=convert(bigint,c.loannum))

View 3 Replies View Related

Bigint Type Primary Key

Jun 19, 2006

I'm using Sql Server 2000/7 for my projects.

How to handle the situation if a primary key (a field set as identity) of type bigint gives an overflow problem?

Is there any way to reuse identities if a primary key field is set as identity from the database settings? (we can do this if we donot set the primary key field as identity from the datbase and handle it from front end)

please help

View 1 Replies View Related

How To Convert Bigint To Timestamp

Mar 23, 2012

I am new SQL Server, I have below Timestamp conversion running in Oracle how do I convert same in SQL Server

Default date - 01/01/1970
Application stores Date as bigint - 1326310811062

(TIMESTAMP('01/01/1970', '00:00:00') + (1326310811062 / 1000) SECONDS) AS CREATION

View 7 Replies View Related

Bigint Autoincrement Question

Apr 15, 2004

Hi All,

i wonder if i can get an bigint autoincrement field where the number begins with the current year + 1 autonumber

Does someone know if it is possible and if yes, how?

Already thanx.
Cheers Wim

View 10 Replies View Related


Jul 23, 2005

I have an SQL back-end with and Access front end. A table in my SQL db hasan auto-num field (integer) that has exceded 70000+ entries. I can add nomore to this table unless I convert that field from INT to BIGINT. Ofcourse, Access doesn't seem to know what a BIGINT is... suggestions?

View 3 Replies View Related

Bigint And Int64 - When Fixed?

Nov 22, 2006

I am attempting to build our first set of packages populating or DW. All of our source system primary keys are bigints (on the main tables at least). SSIS seems to have a problem dealing with bigint values, i.e. having to assign variables as doubles rather than int64, having to cast returning bigint values as float, or bigint sproc output parameters as double. It is all a bit messy (and possibly the most frustrating part of SSIS for me) - does anyone know if this problem is due to be fixed in any forthcoming release?

View 2 Replies View Related

DTS Copy Truncates Bigint Values

Nov 24, 2003

I just discovered that a bigint column in one of my tables is getting it's values truncated when I run a DTS job to copy the data to another database. The DTS job is designed to refresh our test environments from our production database. All the other tables copy fine. This include another table that also contains a bigint column. But this one table consistenly has a problem where 60 or so records are translated from positive number to negative values. The only explaination I came up with was truncation at some point.

Anyone know of a way to fix or work around this?

View 3 Replies View Related

Datetime -&> Binary -&> Bigint (for Sorting)

Apr 20, 2007

this is more of a theoretical question and not necessarily for best practice...though if reliable and efficient, i'd use it.

a lot of my sprocs return xml and are then processed in xslt...so when i need to sort by date (in xslt), i pass the datetime to a function that ultimately returns a bigint...it seems to work, but i'm not 100% confident...

can anyone confirm that the resulting integer values can be relied on for sorting?

function return: cast((cast(@dt as binary(8))) as bigint)

small test:

sql Code:

- sql Code

declare @i int, @d datetime
declare @t table (id int identity, dt datetime, bd binary(8), bi bigint)
set @i=1
set @d = getdate()
while @i between 1 and 20
insert @t(dt, bd, bi)
cast(@d as binary(8)),
cast((cast(@d as binary(8))) as bigint)

set @d = dateadd(mi, @i, @d)
set @i=@i+1

select *
from @t
order by bi

DECLARE @i int, @d datetimeDECLARE @t TABLE (id int identity, dt datetime, bd BINARY(8), bi bigint) SET @i=1SET @d = getdate()WHILE @i BETWEEN 1 AND 20BEGIN    INSERT @t(dt, bd, bi)    SELECT         @d,         CAST(@d AS BINARY(8)),         CAST((CAST(@d AS BINARY(8))) AS bigint)     SET @d = dateadd(mi, @i, @d)    SET @i=@i+1END SELECT *FROM @tORDER BY bi

View 1 Replies View Related

Error When Updating Bigint Columns Using ADO

May 3, 2007

I am attempting to write a conversion of our product for Compact Edition; we already provide it based on SQL Server. The database interface uses ADO through a Python-win32com adaptor, and has worked fine so far. (Note: *not* ADO.net, just plain old COM)

Now, a curious thing happens. When inserting new data through a Recordset, everything works fine - except for columns defined as bigint. There are no exceptions thrown, but when you read the columns back they contain nothing but zeroes. Do the same to any other column type - I've tried integer, numeric, float, nvarchar and ntext so far, and they all seem to work just fine. It does not seem to be conversion-related either, since I've tested the exact same data to various column types. And using bigint on regular SQL Server works just fine.

The code involved is quite unspectacular, and simply switching the column types to integer would solve the immediate problem, but causes potential future issues since we normally store internal IDs in bigint columns, and the values may grow quite large.

View 1 Replies View Related

Variable That Maps To Bigint In SSIS

Sep 11, 2006


Which variable type in SSIS maps to bigint in SQL Server 2005?

I am returning a single column value of type bigint from SQL Server and want to store that in a varible in SSIS, what datatype should I use?

I tried Int32, Int64, Uint64 and it did not work. Did I do something wrong?


View 1 Replies View Related

Unexpected BIGINT Conversion Behavior

Jun 11, 2007

I've recently been struggling with moving a bigint data result from a query into an SSIS variable through the Execute SQL Task. Now, I could just be doing this incorrectly, but I couldn't get it to work at all if I made the variable int64 in SSIS. So here's what I found:

1. bigint from the query into int64 SSIS variable doesn't work at all. It fails.

2. bigint from the query into string SSIS variable works. BUT, it truncates it. And it's a crazy result, at least to me. All of a sudden the number 840550000000000 becomes 8405500000. Now, that's a really unexpected result to me. It seems to me this is a pretty crazy result.

3. Now, if I cast the bigint to varchar in the query and put it into a string SSIS variable, it works perfectly.

Is this a bug? If not, how is this expected behavior? Or maybe I'm just nuts after hours of futzing with this.



View 14 Replies View Related

Convert Or Cast HexaDecimal To Bigint

Aug 21, 2006

Hi ,

I have a hexadecimal string value. I want to convert it to Bigint in Sql Server 2005.

The Hexadecimal value is '0x000000000000000F'.

How is it possible to convert into Bigint.

Please help me

Thanks in advance


View 6 Replies View Related

Return BIGINT OUTPUT Param To VB!?

May 9, 2006

Please help me on this one.

I need to return a value to VB.
I've tried returning a numeric value NUMERIC(25,20) via an output parameter but this didn't work. I'm know at a point in wich I created a bigint and multiplied the value so that the decimals are gone. However it only returns NULL?!?!?!?!!?!?
Here's part of my stored proc

CREATE PROCEDURE dbo.uspCalcWeightedAverage @StartDate2 varchar(10), @EndDate2 varchar(10), @InMarket nvarchar(50), @InProductType int, @InWeekDay int, @WeightedAverage bigint OUTPUTAS......SELECT @WeightedAverage = cast(10000000000 * (SUM(HHF.FACTOR) / COUNT(PDF.FLAG)) as bigint)FROM TBL_PRODUCTDEFS PDF INNER JOIN #DATESBETWEENINTERVAL DBI ON DATEPART(HH, [DBI].[DATE]) = [PDF].[HOUR] INNER JOIN tbl_historichourlyfactors HHF ON DATEPART(D,DBI.DATE) = HHF.DayID AND [PDF].[HOUR] = [HHF].[HOUR] AND DATEPART(M,DBI.DATE) = [HHF].[Month]WHERE PDF.MARKETID = @InMarketID AND PDF.PRODUCTTYPEID = @InProductTypeID AND [PDF].[WD-WE] = @InWeekDay AND HHF.MARKETID = @InMarketID AND PDF.FLAG = 1GROUP BY FLAG

When I retrieve the output param it returns a NULL value. the properties in VB say that the parameter has the following props:
attribute 64 (Long)
NumericScale 0 (Byte)
Precision 19 (Byte)
Type adBigInt
Value Null

I try to return it with the following code (got the code from a friend)

Public Function RunProcedure(ByVal v_strStoredProcName As String, ByRef r_varParamValues() As Variant) As ADODB.RecordsetDim objAdoRecordset As ADODB.RecordsetDim objAdoCommand As ADODB.CommandDim lngCtr As Long On Error GoTo RunCommand_Error ' Create cmd object Set objAdoCommand = New ADODB.Command Set objAdoCommand.ActiveConnection = m_oAdoConnection objAdoCommand.ActiveConnection = m_oAdoConnection objAdoCommand.CommandText = v_strStoredProcName objAdoCommand.CommandType = adCmdStoredProc Call objAdoCommand.Parameters.Refresh 'Stop For lngCtr = 0 To UBound(r_varParamValues) If objAdoCommand.Parameters(lngCtr + 1).Direction = adParamInput Then objAdoCommand.Parameters(lngCtr + 1).Value = r_varParamValues(lngCtr) End If Next Set objAdoRecordset = New ADODB.Recordset objAdoRecordset.CursorLocation = adUseClient Set objAdoRecordset = objAdoCommand.Execute 'Stop For lngCtr = 0 To objAdoCommand.Parameters.Count - 1 If objAdoCommand.Parameters(lngCtr).Direction = adParamOutput Or objAdoCommand.Parameters(lngCtr).Direction = adParamInputOutput Then r_varParamValues(lngCtr - 1) = objAdoCommand.Parameters(lngCtr).Value End If Next Set RunProcedure = objAdoRecordsetRunCommand_Exit: ' Collect your garbage here Exit FunctionRunCommand_Error: ' Collect your garbage here Call g_oGenErr.Throw("WeatherFcst.CDbsConn", "RunCommand")End Function




View 1 Replies View Related

Retrieving The BigInt Value From The Identity Column After Inserting

Jul 26, 2007

I have a database that has a tble with a field that autoincrements as a primary key. meanig that the field type is BigInteger and it is set up as my Identity Column. Now when I insert a new record that field gets updated automaticly.
How can I get this value in the same operation as my insert? meaning, in 1 sub, I insert a new record but then need to retieve the Identity Value. All in the same procedure. 
Waht is the way to achive this please?

View 2 Replies View Related

Core Data Type Question (bigint)

May 10, 2005

We are trying to work with our developers to upgrade to SQL 2000 from SQL 7 for a critical applicaion and all looks good in testing for the most part.  The concern that our developers have is that in order for the application to work on the test SQL 2000 server they had to delete a core data type (bigint) for the application to work.  It doesn't appear to have any negative affects and we know for sure that the application database does not need that data type at all. 
Can someone verify that there are no requirements for SQL 2000 needing to have this data type?   They are worried that something within SQL may rely on it and we would find out the hard way in production possibly.

View 3 Replies View Related

Change Datatype From Varchar To Bigint Not Working

Nov 14, 2007


I would like to change the datatype on a particular column from varchar to bigint across 100's of tables within a database.

I have the command ready which is:


The problem happening is that it seems there are constraints across all the columns in every tables.

The error message is:

Server: Msg 5074, Level 16, State 1, Line 1
The object 'DF__tablename__columnname__0ABD916C' is dependent on column 'columnname'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE ALTER COLUMN columnname failed because one or more objects access this column.

I understand that if I delete this constraint, then it will let me modify the datatype of the column, but since there are tons of them and they are randomly named, how do I achive changing the datatype across multiple tables in bulk.

View 1 Replies View Related

SQL 2012 :: Cast Varchar To Bigint Error

Mar 4, 2014

I try to run code but got an error. How to fix it?

SELECT Cast(('20140304800084500001') AS BIGINT)

Arithmetic overflow error converting expression to data type bigint

View 1 Replies View Related

SQL Server 2012 :: INT Or BIGINT With Leading Zeros

Nov 5, 2015

Looking at an execution plan the conversion of NVARCHAR(15) to BIGINT is a big yellow exclamation NO NO. However, the numbers in the NVARCHAR(15) have leading zeros.

Technically speak 0123456789 is not an INTEGER or BIGINT, the performance of my Stored Procs is there any way to allow leading zeros in a BIGINT Field?

View 5 Replies View Related

Bigint From A SQL Server Table And Viewing With Access

Mar 17, 2008

HI all, we have a table that has it's primary key data type Bigint. Isthere any way with Access to view this table? I find it hard tobelieve that two tools from MS don't play nice with each other, may beI should.SQL Server 2005Access 2003TIA

View 5 Replies View Related

Transact SQL :: Unable To Cast Bigint To Mmm / Dd / YYYY

May 26, 2015

Following SQL statement attempts to convert a bigint to date 

SELECT CAST (DATEADD(SECOND, SEM_AGENT.LAST_UPDATE_TIME /1000 + 8*60*60, '19700101') AS VARCHAR(50)) as Last_Checkin ........

But no matter what I try, the output is 

May 27 2015  1:18AM

and I wish the output to be

May 27 2015

I also tried

SELECT CAST (DATEADD(SECOND, SEM_AGENT.LAST_UPDATE_TIME /1000 + 8*60*60, 'Jan 1, 1970') AS VARCHAR(50)) as Last_Checkin ....

but same result

View 6 Replies View Related

Transact SQL :: Using CAST (column AS BIGINT) And ISNUMERIC

Aug 27, 2015

I found this to work:

SELECT uri, evFieldUri, evFieldVal
, CAST(evFieldVal

[Code] ....

It Returns:

(No column name)
224016  3267      
+000089243829 89243829
224019  2717      
+000089243825 89243825
224472  3333      
+000000000000000000000017     17
225052  3267      
+000089243829 89243829
225055  2717      
+000089243825 89243825

So, then I went back to:

SELECT uri, evFieldUri, evFieldVal
, CAST(evFieldVal

[Code] ....

And it returns this error:

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to bigint.

So, I tried again, and this worked…

SELECT uri, evFieldUri, evFieldVal,CAST(evFieldVal
> 0 AND evFieldUri
IN (1,11))

I logged out and came back and tried again, and it still worked. So then I tried…

SELECT uri, evFieldUri, evFieldVal,CAST(evFieldVal
> 0

[Code] ...

And it fails.

View 5 Replies View Related

Copyrights 2005-15 www.BigResource.com, All rights reserved