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.
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.
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.
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 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.
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)
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!
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? Thanks
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
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))
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)
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?
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?
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.
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)
Original - 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 begin 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+1 end
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
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.
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.
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) Size 0 (ADO_LNGPTR) 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
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? Marc
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.
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:
ALTER TABLE tablename ALTER COLUMN columnname BIGINT
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.
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