Tag Archive: windows 2008

Remote MSSQL…oh, you imbecil! :)


Not very often, you will get the following error:

Error:
The server principal “” is not able to access the database “” under the current security context.

This is because the MS SQL Server is attempting to execute a SQL query to retrieve a list of databases along with  some other formation about those databases. Some of  that information is “Collation”, which, ofcourse you don’t have permission to view for all databases, since  you’re in a shared hosting environment and you can only have access to your database..

Solution is pretty straight forward :
1. In Object Explorer, click on Databases
2. Display Object Explorer Details (F7) or View > Object Explorer Details
3. Right click the column headers and de-select “Collation”

4. Refresh Databases or reconnect to ste machine.

Thats it :)

 

Bye bye  :)

Couple days ago, I had to setup a machine for a customer of ours who will host a web site for some Microsoft conference in Croatia. Since, this is the biggest IT conference in Croatia, I really tried to take everything to the next level and tweak the hell out of that machine :)

For stress testing, I used Siege with  url.txt file where I saved about 20 links from the web site I was testing, in order to emulate the real world scenario as much as possible.

So, for starters, lets throw out some numbers, to see how the machines copes when the IIS install is out of the box :


The test has been run with 150 concurrent users.
Lifting the server siege...      done.                                         Transactions:                    5215 hits
Availability:                 100.00 %
Elapsed time:                  59.83 secs
Data transferred:             472.64 MB
Response time:                  1.69 secs
Transaction rate:              87.16 trans/sec
Throughput:                     7.90 MB/sec
Concurrency:                  147.32
Successful transactions:        5215
Failed transactions:               0
Longest transaction:            5.99
Shortest transaction:           0.25

So, lets take look on the Longest and Shortest transaction value – pretty jumpy, right?

It took the machine to serve the page, in a range of 0.25 to almost 6 seconds? SLOW!

STOP!  Tweaking time :)

One note – do not run 32 bit apps in 64 application pool – waste of resources!

Since this is a website which has around 120 requests to the IIS server for loading one page  I have TURNED OFF the compression!

Why? Simple tradeoff, more bandwidht for less CPU usage. With compression turned on, site had around 1.2 MB ( a lot, I know ), but with compression turned off, site loading time went down for almost 50%, and the site size went for for only 100 KB :)   -  a good trade off in my opinion!

Next -  lets cache this sucker out, I turned on caching for both dynamic and static content, pretty simple setting for a huge improvement!

Now, lets dive into some more complex tweaking -  .Net and registry tweak.

Does the “word”  MaxConcurrentThreadsPerCPU sound familiar?

Basically, with standard out-of-the-box setting, number of requests per CPU is limited to 12.

By default, this entry doesnt exist in the registry, so we need to add it.

MaxConcurrentRequestsPerCPU within HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\ASP.NET\2.0.50727.0 determines the number of concurrent requests per CPU.

Since that value in .NET 4 is already set to 5000, there’s no need to change that is you run a .NET 4 application, but in my case, I had applications with .NET 2, .NET 3.5 and .NET 4 running on the machine, so I have set the value  MaxConcurrentRequestsPerCPU to 0 – which means unlimited.

Lets do some load stress with the new IIS and .NET setting. Again, we will run Siege with 150 concurrent users.
Lifting the server siege...      done.                                         Transactions:                    8171 hits
Availability:                 100.00 %
Elapsed time:                  59.58 secs
Data transferred:             643,80 MB
Response time:                  0.96 secs
Transaction rate:             137.14 trans/sec
Throughput:                     10.73 MB/sec
Concurrency:                  131.67
Successful transactions:        8171
Failed transactions:               0
Longest transaction:            1.99
Shortest transaction:           0.15

So, as we can see, we had almost 3000 more transactions, better transaction rate and load time improved over 3 times ( 0.15 to 1.99 sec ).

But wait, there’s more, in the next post, we will do some network level tweaking, and see if we can get even more juice from the machine.

 

NOTE:  Since I didnt write the applications  and didnt have access to them to review them, I had to optimize the server as much as possible.

So…today I had a very interesting issue. All of a sudden Nagios went red with the message: CRITICAL : – total: 232.82 Gb – used: 212.67 Gb (91%) – free 20.15 Gb (9%) on one of our SQL server. I was like..”NO! It cannot be. I clearly remember that this machine had over 150 Gb of free space yesterday!”

I log in to the machine, and automatically go to the data folder of SQL server thinking that some customer has a HUGE! log file on his database; so, right click – Select All -> Properties… only 50 Gbs of data…hmm…not here!

At that moment I notice that the server isnt “jumpy” as usual, so I start Performance monitor and see that the Avg. Disk Queue is off the chart!  That definatelly isnt right…

Since that machine is using Windows 2003, theres no too good way to see what application is using so much disk I/O ( beside FileMon ) , I start the FileMon and se that the SQL service is writing HUGE amount of data to its ERRORLOG file.

By navigating to the folder, I see that the ERRORLOG file is over 120 Gb big so opening it in Notepad, Context or some other file was out of the question, since it would kill the machine completely.

So, lets turn to powershell once more. In order to be sure to not kill the machine, since its in production , I have moved the file to an empty machine to opened it there.

Powershell : cat errorlog +ENTER :) after that powershell started to list the entire content of the file, and it took about 15 mins until it reached the end. AMAZING

By examining the log, I notice ( it was hard to NOT notice it ) litterally millions of these entries:

Could not allocate space for object ‘dbo.tblComments’.'tblComments’ in database ‘DATABASEname’ because the ‘PRIMARY’ filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

What happened here?

The site that was using that database is poorly written and was allowing everyone to post comments on the articles without any security check, so it soon reached the size limit set on the database.  By further digging on the issue, I saw that the bots are posting about 4-5 comments each second which comes to about 18000 comments/hour…multiply that by 15-16 hours it took to fill the error log to that size, we roughly come to 280000 comments…that’s A LOT of comments! :)

After disabling the site and sending the email to the customer, situation on the machine looks like this :

The big drop on the green line is the moment where I disabled the problematic web site.

Well…in the very beginning, my answer is pretty much simple : yes AND no!  Why you ask?  Just keep reading…

Compression, regardless if its the compression of static or dynamic content is using CPU – that is a fact we can all agree upon.

Lets say compression is a tradeoff – CPU for bandwidth..use more cpu to “burn” less bandwidth.

The big question here is how to decide if you will benefit from turning on compression or not.

Most of us have “multi-purpose” servers which run various roles at the same time ( database server ( MYSQL , MSSQL, etc…) , mail server, web server, etc.. and all of those services are spending a certain amount of CPU time.

By turning on the compression, you will automatically “lose” some CPU time which could have been used for faster execution of some database queries i.e… Read More »

Hello after a very long pause :) . Continuing the “Setuping a server for web hosting” series with the new smashing video of how to install PHP on your machine! AWESOME right? :)

In the very begining of the post, lets learn what PHP is exactly.

So, Wikipedia says ( and I add that I agree with her :) :

PHP: Hypertext Preprocessor is a widely used, general-purpose scripting language that was originally designed for web development to produce dynamic web pages. For this purpose, PHP code is embedded into the HTML source document and interpreted by a web server with a PHP processor module, which generates the web page document. As a general-purpose programming language, PHP code is processed by an interpreter application in command-line mode performing desired operating system operations and producing program output on its standard output channel. It may also function as a graphical application. PHP is available as a processor for most modern web servers and as standalone interpreter on most operating systems and computing platforms.
Read More »